 |
Технология Клиент-Сервер 2010'2
|
|
|
SQL-рецепты: составление отчетов и управление хранилищами данных
Опубликовано: 07.07.2010
Версия текста: 1.1
В данной главе представлены запросы, используемые для создания отчетов. При составлении отчетов обычно применяются специфическое форматирование и различные уровни агрегации. Другой объект рассмотрения данной главы – транспонирование или разворачивание результирующих множеств, преобразование строк в столбцы. Разворачивание – исключительно полезная техника для решения разнообразных задач. Освоив ее, вы найдете ей применение и за рамками вопросов, обсуждаемых здесь.
Разворачивание результирующего множества в одну строку
Задача
Требуется развернуть группу строк, превращая их значения в столбцы. Каждой группе строк должна соответствовать одна строка. Например, имеется результирующее множество, отражающее количество служащих в каждом отделе:
DEPTN OCNT
----- ----
10 3
20 5
30 6
|
Необходимо переформатировать результат так, чтобы множество выглядело следующим образом:
DEPTNO_10 DEPTNO_20 DEPTNO_30
--------- --------- ---------
3 5 6
|
Решение
Транспонируйте результирующее множество с помощью выражения CASE и агрегатной функции SUM:
1 select sum(case when deptno=10 then 1 else 0 end) as deptno_10,
2 sum(case when deptno=20 then 1 else 0 end) as deptno_20,
3 sum(case when deptno=30 then 1 else 0 end) as deptno_30
4 from emp
|
Обсуждение
Данный пример является превосходным введением в разворачивание таблиц. Принцип прост: к каждой строке, возвращенной запросом, применяем выражение CASE, чтобы разложить строки в столбцы. Затем, поскольку стоит конкретная задача пересчитать служащих в каждом отделе, с помощью агрегатной функции SUM подсчитываем количество экземпляров каждого значения DEPTNO. Если что-то не понятно, выполните запрос без агрегатной функции SUM и включите в него DEPTNO для удобства чтения:
select deptno,
case when deptno=10 then 1 else 0 end as deptno_10,
case when deptno=20 then 1 else 0 end as deptno_20,
case when deptno=30 then 1 else 0 end as deptno_30
from emp
order by 1
DEPTNO DEPTNO_10 DEPTNO_20 DEPTNO_30
10 1 0 0
10 1 0 0
10 1 0 0
20 0 1 0
20 0 1 0
20 0 1 0
20 0 1 0
20 0 1 0
30 0 0 1
30 0 0 1
30 0 0 1
30 0 0 1
30 0 0 1
30 0 0 1
|
Выражения CASE, так сказать, расставляют флаги, обозначая, к какому DEPTNO относится строка. На данный момент преобразование «строк в столбцы» уже выполнено. Осталось просто сложить значения, возвращенные в столбцах DEPTNO_10, DEPTNO_20 и DEPTNO_30, и сгруппировать их по DEPTNO. Ниже представлены результаты:
select deptno,
sum(case when deptno=10 then 1 else 0 end) as deptno_10,
sum(case when deptno=20 then 1 else 0 end) as deptno_20,
sum(case when deptno=30 then 1 else 0 end) as deptno_30
from emp group by deptno
DEPTNO DEPTNO_10 DEPTNO_20 DEPTNO_30
10 3 0 0
20 0 5 0
30 0 0 6
|
Если внимательно посмотреть на это результирующее множество, то станет ясно, что с логической точки зрения такой вывод имеет смысл: например, в столбце DEPTNO 10 для DEPTNO_10 указано 3 служащих и нуль для других отделов. Поскольку поставлена цель возвратить одну строку, последний шаг – убрать DEPTNO и GROUP BY и просто суммировать выражения CASE:
select sum(case when deptno=10 then 1 else 0 end) as deptno_10,
sum(case when deptno=20 then 1 else 0 end) as deptno_20,
sum(case when deptno=30 then 1 else 0 end) as deptno_30
from emp
DEPTNO_10 DEPTNO_20 DEPTNO_30
3 5 6
|
Далее представлен другой подход, который иногда применяют для решения такого рода задач:
select max(case when deptno=10 then empcount else null end) as deptno_10
max(case when deptno=20 then empcount else null end) as deptno_20,
max(case when deptno=30 then empcount else null end) as deptno_30
from (
select deptno, count(*) as empcount
from emp group by deptno
) x
|
В этом подходе для подсчета количества служащих в отделе используется вложенный запрос. Выражения CASE основного запроса преобразуют строки в столбцы, обеспечивая следующие результаты:
DEPTNO_10 DEPTNO_20 DEPTNO_30
3 NULL NULL
NULL 5 NULL
NULL NULL 6
|
Затем функция MAX сворачивает столбцы в одну строку:
DEPTNO_10 DEPTNO_20 DEPTNO_30
3 5 6
|
Разворачивание результирующего множества в несколько строк
Задача
Требуется преобразовать строки в столбцы, создавая для каждого значения заданного столбца отдельный столбец. Однако, в отличие от предыдущего рецепта, выведено должно быть несколько строк.
Например, требуется выбрать всех служащих и их должности (JOB). В нашем распоряжении имеется следующее результирующее множество:
JOB ENAME
ANALYST SCOTT
ANALYST FORD
CLERK SMITH
CLERK ADAMS
CLERK MILLER
CLERK JAMES
MANAGER JONES
MANAGER CLARK
MANAGER BLAKE
PRESIDENT KING
SALESMAN ALLEN
SALESMAN MARTIN
SALESMAN TURNER
SALESMAN WARD
|
Хотелось бы отформатировать это множество так, чтобы каждая должность была представлена отдельным столбцом:
CLERKS ANALYSTS MGRS PREZ SALES
MILLER FORD CLARK KING TURNER
JAMES SCOTT BLAKE MARTIN
ADAMS JONES WARD
SMITH ALLEN
|
Решение
В отличие от первого рецепта данной главы данное результирующее множество состоит из нескольких строк. Техника, применявшаяся в предыдущем рецепте, здесь не подходит, потому что обеспечит возвращение MAX(ENAME) для каждого JOB, т. е. одного ENAME для каждого JOB (т. е. мы получим одну строку, как в первом рецепте). Чтобы решить поставленную задачу, необходимо сделать каждое сочетание JOB/ENAME уникальным. Тогда при использовании агрегатной функции для удаления значений NULL не будет утрачено ни одно значение ENAME.
DB2, Oracle и SQL Server
Используя ранжирующую функцию ROW_NUMBER OVER, сделайте каждое сочетание JOB/ENAME уникальным. Разверните результирующее множество с помощью выражения CASE и агрегатной функции MAX, группируя при этом по значению, возвращенному ранжирующей функцией:
1 select max(case when job='CLERK'
2 then ename else null end) as clerks,
3 max(case when job='ANALYST'
4 then ename else null end) as analysts,
5 max(case when job='MANAGER'
6 then ename else null end) as mgrs,
7 max(case when job='PRESIDENT'
8 then ename else null end) as prez,
9 max(case when job='SALESMAN'
10 then ename else null end) as sales
11 from (
12 select job,
13 ename,
14 row_number()over(partition by job order by ename) rn
15 from emp
16 ) x
17 group by rn
|
PostgreSQL и MySQL
Скалярным подзапросом ранжируйте всех служащих по EMPNO. Разверните результирующее множество с помощью выражения CASE и агрегатной функции MAX, группируя при этом по значению, возвращенному скалярным подзапросом:
1 select max(case when job='CLERK'
2 then ename else null end) as clerks,
3 max(case when job='ANALYST'
4 then ename else null end) as analysts,
5 max(case when job='MANAGER'
6 then ename else null end) as mgrs,
7 max(case when job='PRESIDENT'
8 then ename else null end) as prez,
9 max(case when job='SALESMAN'
10 then ename else null end) as sales
11 from (
12 select e.job,
13 e.ename,
14 (select count(*) from emp d
15 where e.job=d.job and e.empno < d.empno) as rnk
16 from emp e
17 ) x
18 group by rnk
|
Обсуждение
DB2, Oracle и SQL Server
Первый шаг – с помощью ранжирующей функции ROW_NUMBER OVER сделать каждое сочетание JOB/ENAME уникальным:
select job,
ename,
row_number()over(partition by job order by ename) rn
from emp
JOB ENAME RN
--------- ------ ---
ANALYST FORD 1
ANALYST SCOTT 2
CLERK ADAMS 1
CLERK JAMES 2
CLERK MILLER 3
CLERK SMITH 4
MANAGER BLAKE 1
MANAGER CLARK 2
MANAGER JONES 3
PRESIDENT KING 1
SALESMAN ALLEN 1
SALESMAN MARTIN 2
SALESMAN TURNER 3
SALESMAN WARD 4
|
Присвоение каждому ENAME уникального для данной должности «номера строки» предотвращает появление любых проблем, которые могли бы возникнуть в случае существования двух служащих с одинаковыми именем и должностью. Целью является обеспечение возможности группировки по номеру строки (по столбцу RN) без исключения служащих из результирующего множества изза применения функции MAX. Данный шаг – самый важный в решении поставленной задачи. Если не выполнить этот первый шаг, внешний запрос в результате агрегации удалит нужные строки. Рассмотрим, как выглядело бы результирующее множество без использования функции ROW_NUMBER OVER, если бы применялась техника, представленная в первом рецепте:
select max(case when job='CLERK'
then ename else null end) as clerks,
max(case when job='ANALYST'
then ename else null end) as analysts,
max(case when job='MANAGER'
then ename else null end) as mgrs,
max(case when job='PRESIDENT'
then ename else null end) as prez,
max(case when job='SALESMAN'
then ename else null end) as sales
from emp
CLERKS ANALYSTS MGRS PREZ SALES
SMITH SCOTT JONES KING WARD
|
К сожалению, для каждого значения JOB возвращена только одна строка: служащий с наивысшим рангом. При разворачивании результирующего множества функции MIN или MAX должны использоваться только как средства для удаления значений NULL, без ограничения возвращаемых значений ENAME. Как этого добиться, станет понятно в ходе обсуждения.
Следующий шаг – использовать выражение CASE для распределения значений ENAME по соответствующим столбцам (JOB):
select rn,
case when job='CLERK'
then ename else null end as clerks,
case when job='ANALYST'
then ename else null end as analysts,
case when job='MANAGER'
then ename else null end as mgrs,
case when job='PRESIDENT'
then ename else null end as prez,
case when job='SALESMAN'
then ename else null end as sales
from (
select job,
ename,
row_number()over(partition by job order by ename) rn
from emp
) x
RN CLERKS ANALYSTS MGRS PREZ SALES
1 FORD
2 SCOTT
1 ADAMS
2 JAMES
3 MILLER
4 SMITH
1 BLAKE
2 CLARK
3 JONES
1 KING
1 ALLEN
2 MARTIN
3 TURNER
4 WARD
|
На данный момент строки транспонированы в столбцы, осталось лишь удалить значения NULL, чтобы сделать результирующее множество более удобным для восприятия. Удаляем значения NULL с помощью агрегатной функции MAX и группируем результаты по RN. (Можно использовать и функцию MIN. Выбор MAX произволен, поскольку в каждой группе всегда осуществляется агрегация всего одного значения.) Сочетания значений RN/JOB/ENAME уникальны. Группировка по RN в сочетании с выражениями CASE, вложенными в вызовы MAX, гарантирует, что каждый вызов MAX обеспечит выбор единственного имени из группы, все остальные значения которой являются значениями NULL:
select max(case when job='CLERK'
then ename else null end) as clerks,
max(case when job='ANALYST'
then ename else null end) as analysts,
max(case when job='MANAGER'
then ename else null end) as mgrs,
max(case when job='PRESIDENT'
then ename else null end) as prez,
max(case when job='SALESMAN'
then ename else null end) as sales
from (
select job,
ename,
row_number()over(partition by job order by ename) rn
from emp
) x
group by rn
CLERKS ANALYSTS MGRS PREZ SALES
MILLER FORD CLARK KING TURNER
JAMES SCOTT BLAKE MARTIN
ADAMS JONES WARD
SMITH ALLEN
|
Методика использования ROW_NUMBER OVER для создания уникальных сочетаний строк исключительно полезна для форматирования результатов запросов. Рассмотрим запрос, создающий разреженный отчет, в котором служащие распределены по DEPTNO и JOB:
select deptno dno, job,
max(case when deptno=10
then ename else null end) as d10,
max(case when deptno=20
then ename else null end) as d20,
max(case when deptno=30
then ename else null end) as d30,
max(case when job='CLERK'
then ename else null end) as clerks,
max(case when job='ANALYST'
then ename else null end) as anals,
max(case when job='MANAGER'
then ename else null end) as mgrs,
max(case when job='PRESIDENT'
then ename else null end) as prez,
max(case when job='SALESMAN'
then ename else null end) as sales
from (
select deptno,
job,
ename,
row_number()over(partition by job order by ename) rn_job,
row_number()over(partition by job order by ename) rn_deptno
from emp
) x
group by deptno, job, rn_deptno, rn_job
order by 1
DNO JOB D10 D20 D30 CLERKS ANALS MGRS PREZ SALES
10 CLERK MILLER MILLER
10 MANAGER CLARK CLARK
10 PRESIDENT KING KING
20 ANALYST FORD FORD
20 ANALYST SCOTT SCOTT
20 CLERK ADAMS ADAMS
20 CLERK SMITH SMITH
20 MANAGER JONES JONES
30 CLERK JAMES JAMES
30 MANAGER BLAKE BLAKE
30 SALESMAN ALLEN ALLEN
30 SALESMAN MARTIN MARTIN
30 SALESMAN TURNER TURNER
30 SALESMAN WARD WARD
|
Просто меняя значения, по которым происходит группировка (следовательно, и не участвующие в агрегации элементы списка SELECT), можно создавать отчеты разных форматов. Стоит потратить немного времени и поэкспериментировать, изменяя эти значения, чтобы понять, как меняются форматы в зависимости от того, что входит в конструкцию GROUP BY.
PostgreSQL и MySQL
Подход к решению для этих СУБД аналогичен используемому для всех остальных: создание уникальных пар JOB/ENAME. Первый шаг – с помощью скалярного подзапроса снабдить каждое сочетание JOB/ ENAME «порядковым номером», или «рангом»:
select e.job,
e.ename,
(select count(*) from emp d
where e.job=d.job and e.empno < d.empno) as rnk from emp e
JOB ENAME RNK
_________ _____ ___
CLERK SMITH 3
SALESMAN ALLEN 3
SALESMAN WARD 2
MANAGER JONES 2
SALESMAN MARTIN 1
MANAGER BLAKE 1
MANAGER CLARK 0
ANALYST SCOTT 1
PRESIDENT KING 0
SALESMAN TURNER 0
CLERK ADAMS 2
CLERK JAMES 1
ANALYST FORD 0
CLERK MILLER 0
|
Присвоение каждому сочетанию JOB/ENAME уникального «ранга» делает каждую строку уникальной. Даже если есть служащие, имеющие одинаковые имена и занимающие одну должность, не будет двух служащих с одним рангом для данной должности. Этот шаг является самым важным при решении задачи. Если не выполнить этот первый шаг, внешний запрос в результате агрегации удалит нужные строки. Рассмотрим, как выглядело бы результирующее множество без присвоения ранга каждому сочетанию JOB/ENAME, если бы применялась техника, представленная в первом рецепте:
select max(case when job='CLERK'
then ename else null end) as clerks,
max(case when job='ANALYST'
then ename else null end) as analysts,
max(case when job='MANAGER'
then ename else null end) as mgrs,
max(case when job='PRESIDENT'
then ename else null end) as prez,
max(case when job='SALESMAN'
then ename else null end) as sales
from emp
CLERKS ANALYSTS MGRS PREZ SALES
SMITH SCOTT JONES KING WARD
|
К сожалению, для каждого значения JOB возвращена только одна строка: служащий с наивысшим рангом. При разворачивании результирующего множества функции MIN или MAX должны использоваться только как средства для удаления значений NULL, без ограничения возвращаемых значений ENAME.
Теперь, когда ясен смысл назначения рангов, можно идти далее. Следующий шаг – использовать выражение CASE для распределения значений ENAME по соответствующим столбцам (JOB):
select rnk,
case when job='CLERK'
then ename else null end as clerks,
case when job='ANALYST'
then ename else null end as analysts,
case when job='MANAGER'
then ename else null end as mgrs,
case when job='PRESIDENT'
then ename else null end as prez,
case when job='SALESMAN'
then ename else null end as sales
from (
select e.job,
e.ename,
(select count(*) from emp d
where e.job=d.job and e.empno < d.empno) as rnk
from emp e
) x
RNK CLERKS ANALYSTS MGRS PREZ SALES
3 SMITH
3 ALLEN
2 WARD
2 JONES
1 MARTIN
1 BLAKE
0 CLARK
1 SCOTT
0 KING
0 TURNER
2 ADAMS
1 JAMES
0 FORD
0 MILLER
|
На данный момент строки транспонированы в столбцы, осталось лишь удалить значения NULL, чтобы сделать результирующее множество более удобным для восприятия. Удаляем значения NULL с помощью агрегатной функции MAX и группируем результаты по RNK. (Выбор MAX произволен. Можно использовать и функцию MIN.) Сочетания значений RN/JOB/ENAME уникальны, поэтому агрегатная функция просто удалит значения NULL:
select max(case when job='CLERK'
then ename else null end) as clerks,
max(case when job='ANALYST'
then ename else null end) as analysts,
max(case when job='MANAGER'
then ename else null end) as mgrs,
max(case when job='PRESIDENT'
then ename else null end) as prez,
max(case when job='SALESMAN'
then ename else null end) as sales
from (
select e.job,
e.ename,
(select count(*) from emp d
where e.job=d.job and e.empno < d.empno) as rnk from emp e
) x
group by rnk
CLERKS ANALYSTS MGRS PREZ SALES
MILLER FORD CLARK KING TURNER
JAMES SCOTT BLAKE MARTIN
ADAMS JONES WARD
SMITH ALLEN
|
Обратное разворачивание результирующего множества
Задача
Требуется преобразовать столбцы в строки. Рассмотрим следующее результирующее множество:
DEPTNO_10 DEPTNO_20 DEPTNO_30
3 5 6
|
Оно должно быть преобразовано к такому виду:
DEPTNO COUNTS_BY_DEPT
10 3
20 5
30 6
|
Решение
Чтобы получить требуемое результирующее множество, можно просто выполнить операции COUNT и GROUP BY для таблицы EMP. Однако главное здесь – понимать, что данные не хранятся как строки; возможно, данные денормализованы и агрегированные значения хранятся как множество столбцов.
Чтобы преобразовать столбцы в строки, используйте декартово произведение. Сколько столбцов требуется преобразовать в строки, должно быть известно заранее, потому что кардинальность табличного выражения, используемого для создания декартова произведения, должна, как минимум, равняться числу транспонируемых столбцов.
Мы не будем создавать денормализованную таблицу данных. В решении данного рецепта применим решение из первого рецепта главы и создадим «широкое» результирующее множество. Вот полное решение:
1 select dept.deptno,
2 case dept.deptno
3 when 10 then emp_cnts.deptno_10
4 when 20 then emp_cnts.deptno_20
5 when 30 then emp_cnts.deptno_30
6 end as counts_by_dept
7 from (
8 select sum(case when deptno=10 then 1 else 0 end) as deptno_10,
9 sum(case when deptno=20 then 1 else 0 end) as deptno_20,
10 sum(case when deptno=30 then 1 else 0 end) as deptno_30
11 from emp
12 ) emp_cnts,
13 (select deptno from dept where deptno <= 30) dept
|
Обсуждение
Вложенный запрос EMP_CNTS является денормализованным представлением, или «широким» результирующим множеством, которое требуется преобразовать в строки. Оно показано ниже:
select sum(case when deptno=10 then 1 else 0 end) as deptno_10,
sum(case when deptno=20 then 1 else 0 end) as deptno_20,
sum(case when deptno=30 then 1 else 0 end) as deptno_30
from emp
DEPTNO_10 DEPTNO_20 DEPTNO_30
3 5 6
|
Поскольку здесь три столбца, будет создано три строки. Начнем с декартова произведения между вложенным запросом EMP_CNTS и некоторым табличным выражением, имеющим, по крайней мере, три строки. В следующем коде для создания декартова произведения используется таблица DEPT. В DEPT четыре строки:
select dept.deptno,
emp_cnts.deptno_10,
emp_cnts.deptno_20,
emp_cnts.deptno_30
from (
select sum(case when deptno=10 then 1 else 0 end) as deptno_10,
sum(case when deptno=20 then 1 else 0 end) as deptno_20,
sum(case when deptno=30 then 1 else 0 end) as deptno_30
from emp
) emp_cnts,
(select deptno from dept where deptno <= 30) dept
|
Декартово произведение позволяет получить по строке для каждого столбца вложенного запроса EMP_CNTS. Поскольку в окончательное результирующее множество должны войти только значения DEPTNO и количество служащих в соответствующем DEPTNO, используем выражение CASE для преобразования трех столбцов в один:
select dept.deptno,
case dept.deptno
when 10 then emp_cnts.deptno_10
when 20 then emp_cnts.deptno_20
when 30 then emp_cnts.deptno_30
end as counts_by_dept
from (
select sum(case when deptno=10 then 1 else 0 end) as deptno_10,
sum(case when deptno=20 then 1 else 0 end) as deptno_20,
sum(case when deptno=30 then 1 else 0 end) as deptno_30
from emp
) emp_cnts,
(select deptno from dept where deptno <= 30) dept
DEPTNO COUNTS_BY_DEPT
10 3
20 5
30 6
|
Обратное разворачивание результирующего множества в один столбец
Задача
Требуется вывести все возвращаемые запросом столбцы в одном столбце. Например, стоит задача получить ENAME, JOB и SAL всех служащих 10го отдела (DEPTNO 10), все три значения должны быть выведены в одном столбце в трех строках для каждого служащего, и значения для разных служащих должны быть разделены пустой строкой. Ожидается получить следующее результирующее множество:
EMPS
CLARK
MANAGER
2450
KING
PRESIDENT
5000
MILLER
CLERK
1300
|
Решение
Ключ к решению – использовать декартово произведение и возвратить по четыре строки для каждого служащего. Это позволит разместить значения столбцов в отдельных строках и разделить значения, относящиеся к разным служащим, пустыми строками.
DB2, Oracle и SQL Server
С помощью ранжирующей функции ROW_NUMBER OVER присвойте каждой строке ранг на основании значений EMPNO (1–4). Затем используйте выражение CASE для преобразования трех столбцов в один:
1 select case rn
2 when 1 then ename
3 when 2 then job
4 when 3 then cast(sal as char(4))
5 end emps
6 from (
7 select e.ename,e.job,e.sal,
8 row_number()over(partition by e.empno
9 order by e.empno) rn
10 from emp e,
11 (select *
12 from emp where job='CLERK') four_rows
13 where e.deptno=10
14 ) x
|
PostgreSQL и MySQL
Данный рецепт призван обратить внимание на применение ранжирующих функций для ранжирования строк, которое затем используется при разворачивании таблицы. На момент написания данной книги ни PostgreSQL, ни MySQL не поддерживают ранжирующие функции.
Обсуждение
DB2, Oracle и SQL Server
Первый шаг – с помощью ранжирующей функции ROW_NUMBER OVER присвоить ранг каждому служащему DEPTNO 10:
select e.ename,e.job,e.sal,
row_number()over(partition by e.empno
order by e.empno) rn
from emp e
where e.deptno=10
|
Пока что ранги ничего не значат. Сегментирование выполнялось по EMPNO, поэтому всем трем служащим DEPTNO 10 присвоен ранг 1. Как только будет введено декартово произведение, появятся разные ранги, что можно видеть в следующих результатах:
select e.ename,e.job,e.sal,
row_number()over(partition by e.empno
order by e.empno) rn
from emp e,
(select * from emp where job='CLERK') four_rows
where e.deptno=10
ENAME JOB SAL RN
CLARK MANAGER 2450 1
CLARK MANAGER 2450 2
CLARK MANAGER 2450 3
CLARK MANAGER 2450 4
KING PRESIDENT 5000 1
KING PRESIDENT 5000 2
KING PRESIDENT 5000 3
KING PRESIDENT 5000 4
MILLER CLERK 1300 1
MILLER CLERK 1300 2
MILLER CLERK 1300 3
MILLER CLERK 1300 4
|
Здесь следует остановиться и понять два ключевых момента:
- RN равен 1 теперь не для всех служащих; теперь это повторяющаяся последовательность значений от 1 до 4, потому что ранжирующие функции применяются после выполнения операторов FROM и WHERE. Таким образом, сегментирование по EMPNO обусловливает сброс RN в начальное значение (1) для записи нового служащего.
- Вложенный запрос FOUR_ROWS – это просто SQL-выражение, возвращающее четыре строки. Это все, что оно делает. Должно быть получено по строке для каждого столбца (ENAME, JOB, SAL) плюс строка-пробел.
На данный момент вся тяжелая работа выполнена, осталось лишь с помощью выражения CASE разместить значения ENAME, JOB и SAL всех служащих в один столбец (чтобы значения SAL могли использоваться в CASE, их необходимо привести к строковому типу):
select case rn
when 1 then ename
when 2 then job
when 3 then cast(sal as char(4))
end emps
from (
select e.ename,e.job,e.sal,
row_number()over(partition by e.empno
order by e.empno) rn
from emp e,
(select *
from emp where job='CLERK') four_rows
where e.deptno=10
) x
|
EMPS
CLARK
MANAGER
2450
KING
PRESIDENT
5000
MILLER
CLERK
1300
|
Исключение повторяющихся значений из результирующего множества
Задача
При формировании отчета выдвинуто требование о том, что дублирующиеся значения в столбце должны отображаться лишь один раз. Например, из таблицы EMP требуется извлечь значения DEPTNO и ENAME, при этом необходимо сгруппировать вместе все строки для каждого значения DEPTNO и выводить каждое значение DEPTNO только один раз. Ожидается получить следующее результирующее множество:
DEPTNO ENAME
10 CLARK
KING
MILLER
20 SMITH
ADAMS
FORD
SCOTT
JONES
30 ALLEN
BLAKE
MARTIN
JAMES
TURNER
WARD
|
Решение
Это простая задача по форматированию, которая без труда решается применением оконной функции LAG OVER, предоставляемой Oracle. Можно прибегнуть к другим средствам, например, скалярным подзапросам и другим оконным функциям (именно они будут использоваться для остальных платформ), но LAG OVER наиболее удобна и уместна в данном случае.
DB2 и SQL Server
С помощью оконной функции MIN OVER можно найти наименьшее значение EMPNO для каждого DEPTNO, затем, используя выражение CASE, «стереть» значение DEPTNO из строк со всеми остальными EMPNO:
1 select case when empno=min_empno
2 then deptno else null
3 end deptno,
4 ename
5 from (
6 select deptno,
7 min(empno)over(partition by deptno) min_empno,
8 empno,
9 ename
10 from emp
11 ) x
|
Oracle
С помощью оконной функции LAG OVER организуйте доступ к предыдущим относительно текущей строкам, чтобы найти первое значение DEPTNO для каждого сегмента:
1 select to_number(
2 decode(lag(deptno)over(order by deptno),
3 deptno,null,deptno)
4 ) deptno, ename
5 from emp
|
PostgreSQL и MySQL
Данный рецепт описывает применение оконных функций для упрощения доступа к строкам, окружающим текущую. На момент написания данной книги эти производители не поддерживают оконные функции.
Обсуждение
DB2 и SQL Server
Первый шаг – с помощью оконной функции MIN OVER найти наименьшее значение EMPNO для каждого DEPTNO:
select deptno,
min(empno)over(partition by deptno) min_empno,
empno,
ename
from emp
DEPTNO MIN_EMPNO EMPNO ENAME
10 7782 7782 CLARK
10 7782 7839 KING
10 7782 7934 MILLER
20 7369 7369 SMITH
20 7369 7876 ADAMS
20 7369 7902 FORD
20 7369 7788 SCOTT
20 7369 7566 JONES
30 7499 7499 ALLEN
30 7499 7698 BLAKE
30 7499 7654 MARTIN
30 7499 7900 JAMES
30 7499 7844 TURNER
30 7499 7521 WARD
|
Следующий и последний шаг – посредством выражения CASE обеспечить однократное отображение DEPTNO. Если значение EMPNO служащего соответствует MIN_EMPNO, возвращается DEPTNO; в противном случае возвращается NULL:
select case when empno=min_empno
then deptno else null
end deptno,
ename
from (
select deptno,
min(empno)over(partition by deptno) min_empno,
empno,
ename
from emp
) x
|
DEPTNO ENAME
10 CLARK
KING
MILLER
20 SMITH
ADAMS
FORD
SCOTT
JONES
30 ALLEN
BLAKE
MARTIN
JAMES
TURNER
WARD
|
Oracle
Первый шаг – с помощью оконной функции LAG OVER возвратить предыдущее значение DEPTNO для каждой строки:
select lag(deptno)over(order by deptno) lag_deptno,
deptno,
ename
from emp
LAG_DEPTNO DEPTNO ENAME
10 CLARK
10 10 KING
10 10 MILLER
10 20 SMITH
20 20 ADAMS
20 20 FORD
20 20 SCOTT
20 20 JONES
20 30 ALLEN
30 30 BLAKE
30 30 MARTIN
30 30 JAMES
30 30 TURNER
30 30 WARD
|
Взглянув на представленное выше результирующее множество, можно сразу увидеть, где значения DEPTNO и LAG_ DEPTNO совпадают. Для этих строк DEPTNO должно быть присвоено значение NULL. Делается это с помощью функции DECODE (функция TO_NUMBER включена, чтобы привести значение DEPTNO к числовому типу):
select to_number(
decode(lag(deptno)over(order by deptno),
deptno,null,deptno)
) deptno, ename
from emp
DEPTNO ENAME
10 CLARK
KING
MILLER
20 SMITH
ADAMS
FORD
SCOTT
JONES
30 ALLEN
BLAKE
MARTIN
JAMES
TURNER
WARD
|
Разворачивание результирующего множества для упрощения вычислений
Задача
Требуется выполнить вычисления, в которых участвуют данные нескольких строк. Чтобы упростить задачу, эти строки надо развернуть и превратить в столбцы, так чтобы все необходимые значения располагались в одной строке.
В данных, используемых в этой книге для примера, DEPTNO 20 – отдел с самой высокой совокупной заработной платой, в чем можно убедиться, выполнив следующий запрос:
select deptno, sum(sal) as sal
from emp
group by deptno
DEPTNO SAL
10 8750
20 10875
30 9400
|
Надо вычислить разность между заработными платами DEPTNO 20 и DEPTNO 10 и заработными платами DEPTNO 20 и DEPTNO 30.
Решение
С помощью агрегатной функции SUM и выражения CASE транспонируйте результаты. Затем включите свои выражения в список оператора SELECT:
1 select d20_sal d10_sal as d20_10_diff,
2 d20_sal d30_sal as d20_30_diff
3 from (
4 select sum(case when deptno=10 then sal end) as d10_sal,
5 sum(case when deptno=20 then sal end) as d20_sal,
6 sum(case when deptno=30 then sal end) as d30_sal
7 from emp
8 ) totals_by_dept
|
Обсуждение
Первый шаг – посредством выражений CASE развернуть таблицу и перенести заработные платы для каждого DEPTNO из строк в столбцы:
select case when deptno=10 then sal end as d10_sal,
case when deptno=20 then sal end as d20_sal,
case when deptno=30 then sal end as d30_sal
from emp
D10_SAL D20_SAL D30_SAL
800
1600
1250
2975
1250
2850
2450
3000
5000
1500
1100
950
3000
1300
|
Следующий шаг – суммировать все заработные платы для каждого DEPTNO, применяя агрегатную функцию SUM к каждому выражению CASE:
select sum(case when deptno=10 then sal end) as d10_sal,
sum(case when deptno=20 then sal end) as d20_sal,
sum(case when deptno=30 then sal end) as d30_sal
from emp
D10_SAL D20_SAL D30_SAL
8750 10875 9400
|
Заключительный шаг – просто оформить приведенный выше SQL как вложенный запрос и найти разности.
Создание блоков данных фиксированного размера
Задача
Требуется организовать данные в одинаковые по размеру блоки с предопределенным количеством элементов в каждом блоке. Общее число блоков может быть неизвестно, но каждый из них должен гарантированно содержать пять элементов. Например, необходимо организовать служащих из таблицы EMP в группы по пять на основании значения EMPNO, как показано ниже:
GRP EMPNO ENAME
1 7369 SMITH
1 7499 ALLEN
1 7521 WARD
1 7566 JONES
1 7654 MARTIN
2 7698 BLAKE
2 7782 CLARK
2 7788 SCOTT
2 7839 KING
2 7844 TURNER
3 7876 ADAMS
3 7900 JAMES
3 7902 FORD
3 7934 MILLER
|
Решение
Решение данной задачи существенно упрощается, если СУБД обеспечивает функции для ранжирования строк. Когда строки ранжированы, для создания блоков по пять строк остается только выполнить деление и определить верхнюю границу для частного.
DB2, Oracle и SQL Server
Используйте ранжирующую функцию ROW_NUMBER OVER, чтобы ранжировать служащих по EMPNO. Затем, чтобы создать группы, разделите полученные ранги на 5 (для SQL Server будет использоваться не функция CEIL, а функция CEILING):
1 select ceil(row_number()over(order by empno)/5.0) grp,
2 empno,
3 ename
4 from emp
|
PostgreSQL и MySQL
С помощью скалярного подзапроса ранжируйте строки по EMPNO. Затем разделите полученные ранги на 5, чтобы создать группы:
1 select ceil(rnk/5.0) as grp,
2 empno, ename
3 from (
4 select e.empno, e.ename,
5 (select count(*) from emp d
6 where e.empno < d.empno)+1 as rnk
7 from emp e
8 ) x
9 order by grp
|
Обсуждение
DB2, Oracle и SQL Server
Ранжирующая функция ROW_NUMBER OVER присваивает ранги или «порядковые номера» строкам, сортированным по столбцу EMPNO:
select row_number()over(order by empno) rn,
empno,
ename
from emp
RN EMPNO ENAME
1 7369 SMITH
2 7499 ALLEN
3 7521 WARD
4 7566 JONES
5 7654 MARTIN
6 7698 BLAKE
7 7782 CLARK
8 7788 SCOTT
9 7839 KING
10 7844 TURNER
11 7876 ADAMS
12 7900 JAMES
13 7902 FORD
14 7934 MILLER
|
Следующий шаг – применить функцию CEIL (или CEILING) после деления результата ROW_NUMBER OVER на пять. Деление на пять логически организует строки в группы по пять, т. е. пять значений, которые меньше или равны 1; пять значений, которые больше 1, но меньше или равны 2; оставшаяся группа (состоящая из четырех последних строк, поскольку 14, количество строк в таблице EMP, не кратно 5) соответствует значениям, которые больше 2, но меньше или равны 3.
Функция CEIL возвращает наименьшее целое число, которое больше, чем переданное в нее значение; это обеспечит создание групп целых чисел. Результаты деления и применения CEIL представлены ниже. Можно проследить порядок операций слева направо, от RN до DIVISION иGRP:
select row_number()over(order by empno) rn,
row_number()over(order by empno)/5.0 division,
ceil(row_number()over(order by empno)/5.0) grp,
empno,
ename
from emp
RN DIVISION GRP EMPNO ENAME
1 .2 1 7369 SMITH
2 .4 1 7499 ALLEN
3 .6 1 7521 WARD
4 .8 1 7566 JONES
5 1 1 7654 MARTIN
6 1.2 2 7698 BLAKE
7 1.4 2 7782 CLARK
8 1.6 2 7788 SCOTT
9 1.8 2 7839 KING
10 2 2 7844 TURNER
11 2.2 3 7876 ADAMS
12 2.4 3 7900 JAMES
13 2.6 3 7902 FORD
14 2.8 3 7934 MILLER
|
PostgreSQL и MySQL
Первый шаг – использовать скалярный подзапрос, чтобы ранжировать строки по EMPNO:
select (select count(*) from emp d
where e.empno < d.empno)+1 as rnk,
e.empno, e.ename
from emp e
order by 1
RNK EMPNO ENAME
1 7934 MILLER
2 7902 FORD
3 7900 JAMES
4 7876 ADAMS
5 7844 TURNER
6 7839 KING
7 7788 SCOTT
8 7782 CLARK
9 7698 BLAKE
10 7654 MARTIN
11 7566 JONES
12 7521 WARD
13 7499 ALLEN
14 7369 SMITH
|
Следующий шаг – после деления RNK на 5 применить функцию CEIL. Деление на 5 логически организует строки в группы по пять, т. е. пять значений, меньших или равных 1; пять значений, больших 1, но меньших или равных 2; последней группе (состоящей из последних четырех строк, поскольку 14, количество строк в таблице EMP, не кратно 5) соответствуют значения, которые больше 2, но меньше или равны 3. Результаты деления и применения CEIL показаны ниже. Проследить порядок операций можно слева направо, от RNK до GRP:
select rnk,
rnk/5.0 as division,
ceil(rnk/5.0) as grp,
empno, ename
from (
select e.empno, e.ename,
(select count(*) from emp d
where e.empno < d.empno)+1 as rnk
from emp e
) x
order by 1
RNK DIVISION GRP EMPNO ENAME
1 .2 1 7934 MILLER
2 .4 1 7902 FORD
3 .6 1 7900 JAMES
4 .8 1 7876 ADAMS
5 1 1 7844 TURNER
6 1.2 2 7839 KING
7 1.4 2 7788 SCOTT
8 1.6 2 7782 CLARK
9 1.8 2 7698 BLAKE
10 2 2 7654 MARTIN
11 2.2 3 7566 JONES
12 2.4 3 7521 WARD
13 2.6 3 7499 ALLEN
14 2.8 3 7369 SMITH
|
Создание заданного количества блоков
Задача
Требуется организовать данные в определенное число блоков. Например, записи служащих в таблице EMP должны быть разделены на четыре группы. Ниже представлено предполагаемое результирующее множество:
GRP EMPNO ENAME
1 7369 SMITH
1 7499 ALLEN
1 7521 WARD
1 7566 JONES
2 7654 MARTIN
2 7698 BLAKE
2 7782 CLARK
2 7788 SCOTT
3 7839 KING
3 7844 TURNER
3 7876 ADAMS
4 7900 JAMES
4 7902 FORD
4 7934 MILLER
|
Эта задача, обратная предыдущей, где число блоков было неизвестно, но было задано количество элементов в каждом из них. Особенность данного рецепта в том, что мы можем не знать, сколько элементов в каждом блоке, но количество блоков определено заранее.
Решение
Решение этой задачи не составляет труда, если используемая СУБД обеспечивает функции для создания «блоков» строк. Если СУБД не предоставляет таких функций, можно просто ранжировать строки и затем распределить их по блокам соответственно остатку от деления их ранга на n, где n – количество блоков, которое должно быть создано. Если доступна оконная функция NTILE, она будет использоваться для создания заданного числа блоков. NTILE разбивает упорядоченное множество на требуемое число сегментов. При этом, если количество записей не делится на это число нацело, записи «остатка» распределяются в доступные блоки, начиная с первого. Это видно из результирующего множества, которое требуется получить в данном рецепте: блоки 1 и 2 включают по 4 строки, блоки 3 и 4 – по три. Если СУБД не поддерживает NTILE, не надо беспокоиться о том, в какие блоки попадут те или иные строки. Основная цель данного рецепта – создание заданного количества блоков.
DB2
С помощью ранжирующей функции ROW_NUMBER OVER ранжируйте строки по EMPNO, затем соответственно остаткам от деления рангов на 4 организуйте четыре блока:
1 select mod(row_number()over(order by empno),4)+1 grp,
2 empno,
3 ename
4 from emp
5 order by 1
|
Oracle и SQL Server
Для этих баз данных подойдет решение для DB2, но в качестве альтернативы (и проще) для создания четырех блоков можно использовать оконную функцию NTILE:
1 select ntile(4)over(order by empno) grp,
2 empno,
3 ename
4 from emp
|
MySQL и PostgreSQL
Используя рефлексивное объединение, ранжируйте строки по EMPNO, затем соответственно остаткам от деления рангов на 4 организуйте необходимые блоки:
1 select mod(count(*),4)+1 as grp,
2 e.empno,
3 e.ename
4 from emp e, emp d
5 where e.empno >= d.empno
6 group by e.empno,e.ename
7 order by 1
|
Обсуждение
DB2
Первый шаг – с помощью ранжирующей функции ROW_NUMBER OVER ранжируем все строки по EMPNO:
select row_number()over(order by empno) grp,
empno,
ename
from emp
GRP EMPNO ENAME
1 7369 SMITH
2 7499 ALLEN
3 7521 WARD
4 7566 JONES
5 7654 MARTIN
6 7698 BLAKE
7 7782 CLARK
8 7788 SCOTT
9 7839 KING
10 7844 TURNER
11 7876 ADAMS
12 7900 JAMES
13 7902 FORD
14 7934 MILLER
|
Когда все строки получили соответствующий ранг, создаем четыре блока с помощью функции вычисления остатка от деления MOD:
select mod(row_number()over(order by empno),4) grp,
empno,
ename
from emp
GRP EMPNO ENAME
1 7369 SMITH
2 7499 ALLEN
3 7521 WARD
0 7566 JONES
1 7654 MARTIN
2 7698 BLAKE
3 7782 CLARK
0 7788 SCOTT
1 7839 KING
2 7844 TURNER
3 7876 ADAMS
0 7900 JAMES
1 7902 FORD
2 7934 MILLER
|
Последний шаг – добавить единицу к GRP, чтобы нумерация блоков начиналась не с 0, а с 1, и применить ORDER BY по GRP, чтобы сортировать строки по блокам.
Oracle и SQL Server
Всю работу выполняет функция NTILE. Просто передаем в нее число, представляющее требуемое количество блоков, и чудо происходит прямо на наших глазах.
MySQL и PostgreSQL
Первый шаг – сформировать декартово произведение таблицы EMP, так чтобы все значения EMPNO можно было сравнивать между собой (ниже показан лишь фрагмент декартова произведения, потому что оно включает 196 строк (14?14):
select e.empno,
e.ename,
d.empno,
d.ename
from emp e, emp d
EMPNO ENAME EMPNO ENAME
7369 SMITH 7369 SMITH
7369 SMITH 7499 ALLEN
7369 SMITH 7521 WARD
7369 SMITH 7566 JONES
7369 SMITH 7654 MARTIN
7369 SMITH 7698 BLAKE
7369 SMITH 7782 CLARK
7369 SMITH 7788 SCOTT
7369 SMITH 7839 KING
7369 SMITH 7844 TURNER
7369 SMITH 7876 ADAMS
7369 SMITH 7900 JAMES
7369 SMITH 7902 FORD
7369 SMITH 7934 MILLER
...
|
Как видно из приведенного результирующего множества, значение EMPNO служащего SMITH можно сравнить с EMPNO всех остальных служащих таблицы EMP (все EMPNO можно сравнить между собой). Следующий шаг – ограничить декартово произведение только теми значениями EMPNO, которые больше или равны другому EMPNO. Результирующее множество частично (поскольку в нем 105 строк) показано ниже:
select e.empno,
e.ename,
d.empno,
d.ename
from emp e, emp d
where e.empno >= d.empno
EMPNO ENAME EMPNO ENAME
7934 MILLER 7934 MILLER
7934 MILLER 7902 FORD
7934 MILLER 7900 JAMES
7934 MILLER 7876 ADAMS
7934 MILLER 7844 TURNER
7934 MILLER 7839 KING
7934 MILLER 7788 SCOTT
7934 MILLER 7782 CLARK
7934 MILLER 7698 BLAKE
7934 MILLER 7654 MARTIN
7934 MILLER 7566 JONES
7934 MILLER 7521 WARD
7934 MILLER 7499 ALLEN
7934 MILLER 7369 SMITH
...
7499 ALLEN 7499 ALLEN
7499 ALLEN 7369 SMITH
7369 SMITH 7369 SMITH
|
Чтобы показать, как предикат WHERE ограничил декартово произведение, из всего результирующего множества я выбрал только строки (из EMP E) для служащих MILLER, ALLEN и SMITH. Поскольку предикат WHERE, используемый для отсеивания по EMPNO, соответствует условию «больше чем или равно», мы гарантированно получим, по крайней мере, одну строку для каждого служащего, потому что каждое значение EMPNO равно самому себе. Но почему для служащего SMITH (в левой части результирующего множества) получена всего одна строка, тогда как для ALLEN их две и для MILLER их 14? Причина в процедуре сравнения значений EMPNO в предикате WHERE: выбираются значения «больше чем или равные» рассматриваемому. В случае со SMITH нет такого значения EMPNO, которое было бы меньше 7369, поэтому для SMITH возвращается только одна строка. В случае с ALLEN значение EMPNO служащего ALLEN, очевидно, равно самому себе (поэтому возвращена соответствующая строка), но 7499 к тому же больше 7369 (EMPNO служащего SMITH), поэтому для ALLEN возвращено две строки. Значение EMPNO служащего MILLER больше, чем EMPNO всех остальных служащих таблицы EMP (и, конечно, равно самому себе), поэтому для MILLER получаем 14 строк.
Теперь мы можем сравнить все EMPNO и выбрать строки, в которых одно значение больше другого. Используем агрегатную функцию COUNT, чтобы получить рефлексивное объединение, поскольку оно является наиболее выразительным результирующим множеством:
select count(*) as grp,
e.empno,
e.ename
from emp e, emp d
where e.empno >= d.empno
group by e.empno,e.ename
order by 1
GRP EMPNO ENAME
1 7369 SMITH
2 7499 ALLEN
3 7521 WARD
4 7566 JONES
5 7654 MARTIN
6 7698 BLAKE
7 7782 CLARK
8 7788 SCOTT
9 7839 KING
10 7844 TURNER
11 7876 ADAMS
12 7900 JAMES
13 7902 FORD
14 7934 MILLER
|
Итак, строки ранжированы. Теперь, чтобы создать четыре блока, просто добавляем 1 к остатку от деления GRP на 4 (это обеспечит нумерацию сегментов не с 0, а с 1). С помощью оператора ORDER BY упорядочиваем блоки по GRP:
select mod(count(*),4)+1 as grp,
e.empno,
e.ename
from emp e, emp d
where e.empno >= d.empno
group by e.empno,e.ename
order by 1
GRP EMPNO ENAME
1 7900 JAMES
1 7566 JONES
1 7788 SCOTT
2 7369 SMITH
2 7902 FORD
2 7654 MARTIN
2 7839 KING
3 7499 ALLEN
3 7698 BLAKE
3 7934 MILLER
3 7844 TURNER
4 7521 WARD
4 7782 CLARK
4 7876 ADAMS
|
Создание горизонтальных гистограмм
Задача
Требуется с помощью SQL создать горизонтальные гистограммы. Например, поставлена задача отобразить количество служащих в каждом отделе в виде горизонтальной гистограммы, в которой каждый служащий представлен экземпляром символа «*». Должно быть получено следующее результирующее множество:
DEPTNO CNT
10 ***
20 *****
30 ******
|
Решение
Ключ к решению – с помощью агрегатной функции COUNT и группировки по DEPTNO найти количество служащих в каждом отделе. После этого передать значения, возвращенные COUNT, в строковую функцию, которая формирует ряды символов «*».
DB2
Для формирования гистограммы используйте функцию REPEAT (повторить):
1 select deptno,
2 repeat('*',count(*)) cnt
3 from emp
4 group by deptno
|
Oracle, PostgreSQL и MySQL
Для формирования необходимых строк символов «*» используйте функцию LPAD:
1 select deptno,
2 lpad('*',count(*),'*') as cnt
3 from emp
4 group by deptno
|
SQL Server
Гистограмма формируется с помощью функции REPLICATE:
1 select deptno,
2 replicate('*',count(*)) cnt
3 from emp
4 group by deptno
|
Обсуждение
Техника для всех баз данных одинакова. Единственное отличие состоит в строковых функциях, используемых для получения рядов символов «*». В данном обсуждении будем опираться на решение для Oracle, но объяснение правомочно для всех решений.
Первый шаг – подсчитываем количество служащих в каждом отделе:
select deptno,
count(*)
from emp
group by deptno
DEPTNO COUNT(*)
10 3
20 5
30 6
|
Следующий шаг – возвращаем для каждого отдела соответствующее число символов «*», исходя из значения, возвращенного COUNT(*). Для этого просто передаем COUNT(*) как аргумент в строковую функцию LPAD:
select deptno,
lpad('*',count(*),'*') as cnt
from emp
group by deptno
DEPTNO CNT
10 ***
20 *****
30 ******
|
Пользователям PostgreSQL придется явно привести значение, возвращенное COUNT(*), к целому типу, как показано ниже:
select deptno,
lpad('*',count(*)::integer,'*') as cnt
from emp
group by deptno
DEPTNO CNT
10 ***
20 *****
30 ******
|
Приведение необходимо, потому что в PostgreSQL числовой аргумент LPAD обязательно должен быть целым числом.
Создание вертикальных гистограмм
Задача
Требуется создать гистограмму, в которой значения увеличиваются вдоль вертикальной оси снизу вверх. Например, поставлена задача отобразить количество служащих в каждом отделе в виде вертикальной гистограммы, в которой каждый служащий представлен экземпляром символа «*». Должно быть получено следующее результирующее множество:
D10 D20 D30
*
* *
* *
* * *
* * *
* * *
|
Решение
Техника, используемая для решения этой задачи, основана на втором рецепте данной главы, «Разворачивание результирующего множества в несколько строк».
DB2, Oracle и SQL Server
Используйте функцию ROW_NUMBER OVER, чтобы уникально идентифицировать каждый экземпляр «*» для каждого DEPTNO. С помощью агрегатной функции MAX разверните результирующее множество и сгруппируйте его по значениям, возвращенным ROW_NUMBER OVER (пользователи SQL Server не должны применять DESC в операторе ORDER BY):
1 select max(deptno_10) d10,
2 max(deptno_20) d20,
3 max(deptno_30) d30
4 from (
5 select row_number()over(partition by deptno order by empno) rn,
6 case when deptno=10 then '*' else null end deptno_10,
7 case when deptno=20 then '*' else null end deptno_20,
8 case when deptno=30 then '*' else null end deptno_30
9 from emp
10 ) x
11 group by rn
12 order by 1 desc, 2 desc, 3 desc
|
PostgreSQL и MySQL
Используйте скалярный подзапрос, чтобы уникально идентифицировать каждый экземпляр «*» для каждого DEPTNO. Чтобы развернуть результирующее множество, примените агрегатную функцию MAX к значениям, возвращенным вложенным запросом Х, проводя при этом группировку по RNK. Пользователи MySQL не должны использовать DESC в операторе ORDER BY:
1 select max(deptno_10) as d10,
2 max(deptno_20) as d20,
3 max(deptno_30) as d30
4 from (
5 select case when e.deptno=10 then '*' else null end deptno_10,
6 case when e.deptno=20 then '*' else null end deptno_20,
7 case when e.deptno=30 then '*' else null end deptno_30,
8 (select count(*) from emp d
9 where e.deptno=d.deptno and e.empno < d.empno ) as rnk
10 from emp e
11 ) x
12 group by rnk
13 order by 1 desc, 2 desc, 3 desc
|
Обсуждение
DB2, Oracle и SQL Server
Первый шаг – с помощью ранжирующей функции ROW_NUMBER уникально идентифицировать каждый экземпляр «*» для каждого отдела. Возвращаем «*» для каждого служащего каждого отдела посредством выражения CASE:
select row_number()over(partition by deptno order by empno) rn,
case when deptno=10 then '*' else null end deptno_10,
case when deptno=20 then '*' else null end deptno_20,
case when deptno=30 then '*' else null end deptno_30
from emp
RN DEPTNO_10 DEPTNO_20 DEPTNO_30
1 *
2 *
3 *
1 *
2 *
3 *
4 *
5 *
1 *
2 *
3 *
4 *
5 *
6 *
|
Следующий и последний шаг – применить агрегатную функцию MAX к каждому выражению CASE, группируя по RN, чтобы удалить значения NULL из результирующего множества. Упорядочиваем результаты по возрастанию или по убыванию, в зависимости от того, как используемая СУБД сортирует значения NULL:
select max(deptno_10) d10,
max(deptno_20) d20,
max(deptno_30) d30
from (
select row_number()over(partition by deptno order by empno) rn,
case when deptno=10 then '*' else null end deptno_10,
case when deptno=20 then '*' else null end deptno_20,
case when deptno=30 then '*' else null end deptno_30
from emp
) x
group by rn
order by 1 desc, 2 desc, 3 desc
D10 D20 D30
*
* *
* *
* * *
* * *
* * *
|
PostgreSQL и MySQL
Первый шаг – с помощью скалярного подзапроса уникально идентифицировать каждый экземпляр «*» каждого отдела. Скалярный подзапрос ранжирует служащих каждого отдела по EMPNO, поэтому дубликатов быть не может. Возвращаем «*» для каждого служащего каждого отдела посредством выражения CASE:
select case when e.deptno=10 then '*' else null end deptno_10,
case when e.deptno=20 then '*' else null end deptno_20,
case when e.deptno=30 then '*' else null end deptno_30,
(select count(*) from emp d
where e.deptno=d.deptno and e.empno < d.empno ) as rnk
from emp e
DEPTNO_10 DEPTNO_20 DEPTNO_30 RNK
* 4
* 5
* 4
* 3
* 3
* 2
* 2
* 2
* 1
* 1
* 1
* 0
* 0
* 0
|
Затем применяем к каждому выражению CASE агрегатную функцию MAX, группируя по RNK, чтобы удалить из результирующего множества все значения NULL. Упорядочиваем результаты по возрастанию или по убыванию, в зависимости от того, как используемая СУБД сортирует значения NULL.
select max(deptno_10) as d10,
max(deptno_20) as d20,
max(deptno_30) as d30
from (
select case when e.deptno=10 then '*' else null end deptno_10,
case when e.deptno=20 then '*' else null end deptno_20,
case when e.deptno=30 then '*' else null end deptno_30,
(select count(*) from emp d
where e.deptno=d.deptno and e.empno < d.empno ) as rnk
from emp e
) x
group by rnk order by
1 desc, 2 desc, 3 desc
D10 D20 D30
*
* *
* *
* * *
* * *
* * *
|
Как возвратить столбцы, не перечисленные в операторе GROUP BY
Задача
Выполняется запрос с оператором GROUP BY, и в результирующем множестве также требуется возвратить столбцы, не входящие в конструкцию GROUP BY. Обычно это невозможно, поскольку такие столбцы для каждой строки предлагают множество решений, тогда как требуется представить одно значение на строку.
Скажем, неоходимо выбрать служащих, получающих наибольшую и наименьшую заработные платы в каждом отделе и для каждой должности. Должны быть представлены имя каждого такого служащего, отдел, в котором он работает, его должность и заработная плата. Ожидается получить следующее результирующее множество:
DEPTNO ENAME JOB SAL DEPT_STATUS JOB_STATUS
10 MILLER CLERK 1300 LOW SAL IN DEPT TOP SAL IN JOB
10 CLARK MANAGER 2450 LOW SAL IN JOB
10 KING PRESIDENT 5000 TOP SAL IN DEPT TOP SAL IN JOB
20 SCOTT ANALYST 3000 TOP SAL IN DEPT TOP SAL IN JOB
20 FORD ANALYST 3000 TOP SAL IN DEPT TOP SAL IN JOB
20 SMITH CLERK 800 LOW SAL IN DEPT LOW SAL IN JOB
20 JONES MANAGER 2975 TOP SAL IN JOB
30 JAMES CLERK 950 LOW SAL IN DEPT
30 MARTIN SALESMAN 1250 LOW SAL IN JOB
30 WARD SALESMAN 1250 LOW SAL IN JOB
30 ALLEN SALESMAN 1600 TOP SAL IN JOB
30 BLAKE MANAGER 2850 TOP SAL IN DEPT
|
К сожалению, включение всех этих столбцов в оператор SELECT разрушит группировку. Рассмотрим такой пример. Служащий «KING» получает самую высокую заработную плату. Мы хотим убедиться в этом с помощью следующего запроса:
select ename,max(sal)
from emp
group by ename
|
Вместо того чтобы представить запись «KING» и его заработную плату, приведенный выше запрос возвратит все 14 строк таблицы EMP. Причина в группировке: MAX(SAL) применяется к каждому ENAME. Таким образом, кажется, что приведенный выше запрос можно интерпретировать как «найти служащего, получающего наивысшую заработную плату», а на самом деле он «находит наивысшую заработную плату для каждого значения ENAME в таблице EMP». В данном рецепте рассматривается, как можно включить в результирующее множество столбец ENAME, не указывая его в операторе GROUP BY.
Решение
Наибольшую и наименьшую заработные платы по DEPTNO и JOB находим с помощью вложенного запроса. Затем выбираем только тех служащих, которые получают такие зарплаты.
DB2, Oracle и SQL Server
С помощью оконных функций MAX OVER и MIN OVER найдите наибольшую и наименьшую заработные платы по DEPTNO и JOB. Затем выберите строки, в которых заработные платы соответствуют полученным наибольшим и наименьшим значениям:
1 select deptno,ename,job,sal,
2 case when sal = max_by_dept
3 then 'TOP SAL IN DEPT'
4 when sal = min_by_dept
5 then 'LOW SAL IN DEPT'
6 end dept_status,
7 case when sal = max_by_job
8 then 'TOP SAL IN JOB'
9 when sal = min_by_job
10 then 'LOW SAL IN JOB'
11 end job_status
12 from (
13 select deptno,ename,job,sal,
14 max(sal)over(partition by deptno) max_by_dept,
15 max(sal)over(partition by job) max_by_job,
16 min(sal)over(partition by deptno) min_by_dept,
17 min(sal)over(partition by job) min_by_job
18 from emp
19 ) emp_sals
20 where sal in (max_by_dept,max_by_job,
21 min_by_dept,min_by_job)
|
PostgreSQL и MySQL
С помощью скалярных подзапросов найдите наибольшую и наименьшую заработные платы по DEPTNO и JOB. Затем выберите строки только тех служащих, которые получают такие заработные платы:
1 select deptno,ename,job,sal,
2 case when sal = max_by_dept
3 then 'TOP SAL IN DEPT'
4 when sal = min_by_dept
5 then 'LOW SAL IN DEPT'
6 end as dept_status,
7 case when sal = max_by_job
8 then 'TOP SAL IN JOB'
9 when sal = min_by_job
10 then 'LOW SAL IN JOB'
11 end as job_status
12 from (
13 select e.deptno,e.ename,e.job,e.sal,
14 (select max(sal) from emp d
15 where d.deptno = e.deptno) as max_by_dept,
16 (select max(sal) from emp d
17 where d.job = e.job) as max_by_job,
18 (select min(sal) from emp d
19 where d.deptno = e.deptno) as min_by_dept,
20 (select min(sal) from emp d
21 where d.job = e.job) as min_by_job
22 from emp e
23 ) x
24 where sal in (max_by_dept,max_by_job,
25 min_by_dept,min_by_job)
|
Обсуждение
DB2, Oracle и SQL Server
Первый шаг – с помощью оконных функций MAX OVER и MIN OVER находим наибольшие и наименьшие заработные платы по DEPTNO и JOB.
select deptno,ename,job,sal,
max(sal)over(partition by deptno) maxDEPT,
max(sal)over(partition by job) maxJOB,
min(sal)over(partition by deptno) minDEPT,
min(sal)over(partition by job) minJOB
from emp
DEPTNO ENAME JOB SAL MAXDEPT MAXJOB MINDEPT MINJOB
10 MILLER CLERK 1300 5000 1300 1300 800
10 CLARK MANAGER 2450 5000 2975 1300 2450
10 KING PRESIDENT 5000 5000 5000 1300 5000
20 SCOTT ANALYST 3000 3000 3000 800 3000
20 FORD ANALYST 3000 3000 3000 800 3000
20 SMITH CLERK 800 3000 1300 800 800
20 JONES MANAGER 2975 3000 2975 800 2450
20 ADAMS CLERK 1100 3000 1300 800 800
30 JAMES CLERK 950 2850 1300 950 800
30 MARTIN SALESMAN 1250 2850 1600 950 1250
30 TURNER SALESMAN 1500 2850 1600 950 1250
30 WARD SALESMAN 1250 2850 1600 950 1250
30 ALLEN SALESMAN 1600 2850 1600 950 1250
30 BLAKE MANAGER 2850 2850 2975 950 2450
|
Теперь каждую заработную плату можно сравнить с наибольшей и наименьшей для отдела (по DEPTNO) и должности (по JOB). Обратите внимание, что группировка (включение нескольких столбцов в оператор SELECT) не оказывает влияния на возвращаемые функциями MIN OVER и MAX OVER значения. В этом прелесть оконных функций: агрегат вычисляется для заданной «группы» или сегмента и возвращается в каждой строке соответствующей группы. Последний шаг – просто поместить оконные функции во вложенный запрос и выбрать только те строки, которые соответствуют возвращаемым ими значениям. Для отображения «статуса» служащих в окончательном результирующем множестве используйте простое выражение CASE:
select deptno,ename,job,sal,
case when sal = max_by_dept
then 'TOP SAL IN DEPT'
when sal = min_by_dept
then 'LOW SAL IN DEPT'
end dept_status,
case when sal = max_by_job
then 'TOP SAL IN JOB'
when sal = min_by_job
then 'LOW SAL IN JOB'
end job_status
from (
select deptno,ename,job,sal,
max(sal)over(partition by deptno) max_by_dept,
max(sal)over(partition by job) max_by_job,
min(sal)over(partition by deptno) min_by_dept,
min(sal)over(partition by job) min_by_job
from emp
) x
where sal in (max_by_dept,max_by_job,
min_by_dept,min_by_job)
DEPTNO ENAME JOB SAL DEPT_STATUS JOB_STATUS
10 MILLER CLERK 1300 LOW SAL IN DEPT TOP SAL IN JOB
10 CLARK MANAGER 2450 LOW SAL IN JOB
10 KING PRESIDENT 5000 TOP SAL IN DEPT TOP SAL IN JOB
20 SCOTT ANALYST 3000 TOP SAL IN DEPT TOP SAL IN JOB
20 FORD ANALYST 3000 TOP SAL IN DEPT TOP SAL IN JOB
20 SMITH CLERK 800 LOW SAL IN DEPT LOW SAL IN JOB
20 JONES MANAGER 2975 TOP SAL IN JOB
30 JAMES CLERK 950 LOW SAL IN DEPT
30 MARTIN SALESMAN 1250 LOW SAL IN JOB
30 WARD SALESMAN 1250 LOW SAL IN JOB
30 ALLEN SALESMAN 1600 TOP SAL IN JOB
30 BLAKE MANAGER 2850 TOP SAL IN DEPT
|
PostgreSQL и MySQL
Первый шаг – с помощью скалярных подзапросов находим наибольшие и наименьшие заработные платы по DEPTNO и JOB.
select e.deptno,e.ename,e.job,e.sal, (select max(sal) from emp d where d.deptno = e.deptno) as maxDEPT, (select max(sal) from emp d where d.job = e.job) as maxJOB, (select min(sal) from emp d where d.deptno = e.deptno) as minDEPT, (select min(sal) from emp d where d.job = e.job) as minJOB from emp e
DEPTNO ENAME JOB SAL MAXDEPT MAXJOB MINDEPT MINJOB
20 SMITH CLERK 800 3000 1300 800 800
30 ALLEN SALESMAN 1600 2850 1600 950 1250
30 WARD SALESMAN 1250 2850 1600 950 1250
20 JONES MANAGER 2975 3000 2975 800 2450
30 MARTIN SALESMAN 1250 2850 1600 950 1250
30 BLAKE MANAGER 2850 2850 2975 950 2450
10 CLARK MANAGER 2450 5000 2975 1300 2450
20 SCOTT ANALYST 3000 3000 3000 800 3000
10 KING PRESIDENT 5000 5000 5000 1300 5000
30 TURNER SALESMAN 1500 2850 1600 950 1250
20 ADAMS CLERK 1100 3000 1300 800 800
30 JAMES CLERK 950 2850 1300 950 800
20 FORD ANALYST 3000 3000 3000 800 3000
10 MILLER CLERK 1300 5000 1300 1300 800
|
Теперь все заработные платы, представленные в таблице EMP, можно сравнить с наибольшими и наименьшими для отдела (по DEPTNO) и должности (по JOB). Последний шаг – поместить скалярные подзапросы во вложенный запрос и просто выбрать служащих, заработные платы которых соответствуют возвращаемым скалярными подзапросами. Для отображения «статуса» служащих в окончательном результирующем множестве используйте выражение CASE:
select deptno,ename,job,sal,
case when sal = max_by_dept
then 'TOP SAL IN DEPT'
when sal = min_by_dept
then 'LOW SAL IN DEPT'
end as dept_status,
case when sal = max_by_job
then 'TOP SAL IN JOB'
when sal = min_by_job
then 'LOW SAL IN JOB'
end as job_status
from (
select e.deptno,e.ename,e.job,e.sal,
(select max(sal) from emp d
where d.deptno = e.deptno) as max_by_dept,
(select max(sal) from emp d
where d.job = e.job) as max_by_job,
(select min(sal) from emp d
where d.deptno = e.deptno) as min_by_dept,
(select min(sal) from emp d
where d.job = e.job) as min_by_job
from emp e
) x
where sal in (max_by_dept,max_by_job,
min_by_dept,min_by_job)
DEPTNO ENAME JOB SAL DEPT_STATUS JOB_STATUS
10 CLARK MANAGER 2450 LOW SAL IN JOB
10 KING PRESIDENT 5000 TOP SAL IN DEPT TOP SAL IN JOB
10 MILLER CLERK 1300 LOW SAL IN DEPT TOP SAL IN JOB
20 SMITH CLERK 800 LOW SAL IN DEPT LOW SAL IN JOB
20 FORD ANALYST 3000 TOP SAL IN DEPT TOP SAL IN JOB
20 SCOTT ANALYST 3000 TOP SAL IN DEPT TOP SAL IN JOB
20 JONES MANAGER 2975 TOP SAL IN JOB
30 ALLEN SALESMAN 1600 TOP SAL IN JOB
30 BLAKE MANAGER 2850 TOP SAL IN DEPT
30 MARTIN SALESMAN 1250 LOW SAL IN JOB
30 JAMES CLERK 950 LOW SAL IN DEPT
30 WARD SALESMAN 1250 LOW SAL IN JOB
|
Вычисление простых подсумм
Задача
В данном рецепте под «простой подсуммой» подразумевается результирующее множество, содержащее значения, полученные в результате агрегации одного столбца, и общую сумму таблицы. В качестве примера возьмем результирующее множество, содержащее суммы заработных плат таблицы EMP по должностям (JOB), а также сумму всех заработных плат таблицы EMP. Суммы зарплат по JOB – это подсуммы, а сумма всех заработных плат таблицы EMP – это общая сумма. Такое результирующее множество выглядело бы так:
JOB SAL
ANALYST 6000
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
TOTAL 29025
|
Решение
Расширение ROLLUP оператора GROUP BY идеально справляется с этой задачей. Если СУБД не поддерживает ROLLUP, задачу можно решить, хотя и немного сложнее, с помощью скалярного подзапроса или запроса UNION.
DB2 и Oracle
Используя агрегатную функцию SUM, просуммируйте заработные платы; с помощью расширения ROLLUP оператора GROUP BY организуйте результаты в подсуммы (по JOB) и найдите общую сумму (для всей таблицы):
1 select case grouping(job)
2 when 0 then job
3 else 'TOTAL'
4 end job,
5 sum(sal) sal
6 from emp
7 group by rollup(job)
|
SQL Server и MySQL
Используя агрегатную функцию SUM, просуммируйте заработные платы; с помощью WITH ROLLUP организуйте результаты в подсуммы (по JOB) и найдите общую сумму (для всей таблицы). Затем посредством функции COALESCE задайте имя «TOTAL» для строки общей суммы (в противном случае в столбце JOB этой строки будет располагаться значение NULL):
1 select coalesce(job,'TOTAL') job,
2 sum(sal) sal
3 from emp
4 group by job with rollup
|
Для SQL Server для определения уровня агрегации можно использовать вместо COALESCE функцию GROUPING, показанную в рецепте Oracle/DB2.
PostgreSQL
Используя агрегатную функцию SUM, просуммируйте заработные платы по DEPTNO. Затем посредством оператора UNION ALL объедините этот запрос с запросом, вычисляющим сумму всех заработных плат таблицы:
1 select job, sum(sal) as sal
2 from emp
3 group by job
4 union all
5 select 'TOTAL', sum(sal)
6 from emp
|
Обсуждение
DB2 и Oracle
Первый шаг – с помощью агрегатной функции SUM, группируя по столбцу JOB, найти суммы заработных плат для каждой должности (JOB):
select job, sum(sal) sal
from emp
group by job
JOB SAL
ANALYST 6000
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
|
Следующий шаг – использовать расширение ROLLUP оператора GROUP BY для формирования общей суммы всех заработных плат помимо подсумм для каждой JOB:
select job, sum(sal) sal
from emp
group by rollup(job)
JOB SAL
ANALYST 6000
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
29025
|
Последний шаг – применить к столбцу JOB функцию GROUPING для отображения имени поля общей суммы. Если значение JOB – NULL, функция GROUPING возвратит 1, свидетельствующую о том, что значение SAL является общей суммой, созданной ROLLUP. Если значение JOB не NULL, функция GROUPING возвратит 0, свидетельствующий о том, что значение SAL является результатом GROUP BY, а не ROLLUP. Поместите вызов GROUPING(JOB) в выражение CASE, которое будет возвращать либо название должности, либо имя «TOTAL» соответственно:
select case grouping(job)
when 0 then job
else 'TOTAL'
end job,
sum(sal) sal
from emp
group by rollup(job)
JOB SAL
ANALYST 6000
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
TOTAL 29025
|
SQL Server и MySQL
Первый шаг – использовать агрегатную функцию SUM, группируя результаты по JOB, чтобы получить суммы заработных плат по должностям:
select job, sum(sal) sal
from emp
group by job
JOB SAL
ANALYST 6000
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
|
Следующий шаг – использовать расширение ROLLUP оператора GROUP BY, чтобы помимо подсумм для каждой должности найти общую сумму всех заработных плат:
select job, sum(sal) sal
from emp
group by job with rollup
JOB SAL
ANALYST 6000
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
29025
|
Последний шаг – применить функцию COALESCE к столбцу JOB. Если значение JOB – NULL, значение SAL является общей суммой, созданной ROLLUP. Если значение JOB не NULL, значение SAL является результатом «обычного» GROUP BY, а не ROLLUP:
select coalesce(job,'TOTAL') job,
sum(sal) sal
from emp
group by job with rollup
JOB SAL
ANALYST 6000
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
TOTAL 29025
|
PostgreSQL
Первый шаг – сгруппировать результаты, возвращаемые агрегатной функцией SUM, по должностям:
select job, sum(sal) sal
from emp
group by job
JOB SAL
ANALYST 6000
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
|
Последний шаг – используя оператор UNION ALL, найти общую сумму результатов, возвращенных первым запросом:
select job, sum(sal) as sal
from emp
group by job
union all
select 'TOTAL', sum(sal)
from emp
JOB SAL
ANALYST 6000
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
TOTAL 29025
|
Вычисление подсумм для всех возможных сочетаний
Задача
Требуется найти суммы всех заработных плат по отделам (группировка по столбцу DEPTNO), по должностям (группировка по столбцу JOB) и для каждого сочетания JOB/DEPTNO. Должна быть также вычислена общая сумма всех заработных плат таблицы EMP. Необходимо получить следующее результирующее множество:
DEPTNO JOB CATEGORY SAL
10 CLERK TOTAL BY DEPT AND JOB 1300
10 MANAGER TOTAL BY DEPT AND JOB 2450
10 PRESIDENT TOTAL BY DEPT AND JOB 5000
20 CLERK TOTAL BY DEPT AND JOB 1900
30 CLERK TOTAL BY DEPT AND JOB 950
30 SALESMAN TOTAL BY DEPT AND JOB 5600
30 MANAGER TOTAL BY DEPT AND JOB 2850
20 MANAGER TOTAL BY DEPT AND JOB 2975
20 ANALYST TOTAL BY DEPT AND JOB 6000
CLERK TOTAL BY JOB 4150
ANALYST TOTAL BY JOB 6000
MANAGER TOTAL BY JOB 8275
PRESIDENT TOTAL BY JOB 5000
SALESMAN TOTAL BY JOB 5600
10 TOTAL BY DEPT 8750
30 TOTAL BY DEPT 9400
20 TOTAL BY DEPT 10875
GRAND TOTAL FOR TABLE 29025
|
Решение
Расширения оператора GROUP BY, появившиеся в последние годы, значительно упрощают решение этой задачи. Если используемая платформа не поддерживает расширения, позволяющие определять подсуммы различных уровней, придется вычислять их самостоятельно (посредством рефлексивных объединений или скалярных подзапросов).
DB2
Для DB2 потребуется приводить (с помощью функции CAST) результаты, возвращаемые GROUPING, к типу данных CHAR(1):
1 select deptno,
2 job,
3 case cast(grouping(deptno) as char(1))||
4 cast(grouping(job) as char(1))
5 when '00' then 'TOTAL BY DEPT AND JOB'
6 when '10' then 'TOTAL BY JOB'
7 when '01' then 'TOTAL BY DEPT'
8 when '11' then 'TOTAL FOR TABLE'
9 end category,
10 sum(sal)
11 from emp
12 group by cube(deptno,job)
13 order by grouping(job),grouping(deptno)
|
Oracle
Используйте расширение CUBE оператора GROUP BY в сочетании с оператором конкатенации ||:
1 select deptno,
2 job,
3 case grouping(deptno)||grouping(job)
4 when '00' then 'TOTAL BY DEPT AND JOB'
5 when '10' then 'TOTAL BY JOB'
6 when '01' then 'TOTAL BY DEPT'
7 when '11' then 'GRAND TOTAL FOR TABLE'
8 end category,
9 sum(sal) sal
10 from emp
11 group by cube(deptno,job)
12 order by grouping(job),grouping(deptno)
|
SQL Server
Используйте расширение CUBE оператора GROUP BY. Для SQL Server потребуется привести (CAST) результаты, возвращаемые GROUPING, к типу CHAR(1) и использовать оператор конкатенации + (а не оператор ||, применяемый в Oracle):
1 select deptno,
2 job,
3 case cast(grouping(deptno)as char(1))+
4 cast(grouping(job)as char(1))
5 when '00' then 'TOTAL BY DEPT AND JOB'
6 when '10' then 'TOTAL BY JOB'
7 when '01' then 'TOTAL BY DEPT'
8 when '11' then 'GRAND TOTAL FOR TABLE'
9 end category,
10 sum(sal) sal
11 from emp
12 group by deptno,job with cube
13 order by grouping(job),grouping(deptno)
|
PostgreSQL и MySQL
Суммы для разных столбцов и их сочетаний формируются с помощью многократного применения оператора UNION ALL:
1 select deptno, job,
2 'TOTAL BY DEPT AND JOB' as category,
3 sum(sal) as sal
4 from emp
5 group by deptno, job
6 union all
7 select null, job, 'TOTAL BY JOB', sum(sal)
8 from emp
9 group by job
10 union all
11 select deptno, null, 'TOTAL BY DEPT', sum(sal)
12 from emp
13 group by deptno
14 union all
15 select null,null,'GRAND TOTAL FOR TABLE', sum(sal)
16 from emp
|
Обсуждение
Oracle, DB2 и SQL Server
Решения для всех трех СУБД, по сути, одинаковые. Первый шаг – найти суммарные заработные платы для каждого сочетания JOB и DEPTNO, применяя агрегатную функцию SUM и группируя значения по DEPTNO и JOB:
select deptno, job, sum(sal) sal
from emp
group by deptno, job
DEPTNO JOB SAL
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
|
Следующий шаг – вычислить подсуммы по JOB и DEPTNO и общую сумму для всей таблицы. С помощью расширения CUBE оператора GROUP BY осуществляем агрегацию значений SAL по DEPTNO, JOB изатем для всей таблицы:
select deptno,
job,
sum(sal) sal
from emp
group by cube(deptno,job)
DEPTNO JOB SAL
29025
CLERK 4150
ANALYST 6000
MANAGER 8275
SALESMAN 5600
PRESIDENT 5000
10 8750
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 10875
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
30 9400
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
|
Далее используем функцию GROUPING в сочетании с выражением CASE, чтобы представить результаты в более выразительном формате. GROUPING(JOB) возвращает значения 1 или 0 в зависимости от того, получены ли значения SAL оператором GROUP BY или его расширением CUBE. Если значение возвращено CUBE, получаем 1, в противном случае – 0. Аналогично для GROUPING(DEPTNO). Из первого шага решения видим, что группировка выполняется по DEPTNO и JOB. Таким образом, в результате вызова GROUPING для строки, представляющей сочетание DEPTNO и JOB, должен быть возвращен 0. Запрос ниже подтверждает это:
select deptno,
job,
grouping(deptno) is_deptno_subtotal,
grouping(job) is_job_subtotal,
sum(sal) sal
from emp
group by cube(deptno,job)
order by 3,4
DEPTNO JOB IS_DEPTNO_SUBTOTAL IS_JOB_SUBTOTAL SAL
10 CLERK 0 0 1300
10 MANAGER 0 0 2450
10 PRESIDENT 0 0 5000
20 CLERK 0 0 1900
30 CLERK 0 0 950
30 SALESMAN 0 0 5600
30 MANAGER 0 0 2850
20 MANAGER 0 0 2975
20 ANALYST 0 0 6000
10 0 1 8750
20 0 1 10875
30 0 1 9400
CLERK 1 0 4150
ANALYST 1 0 6000
MANAGER 1 0 8275
PRESIDENT 1 0 5000
SALESMAN 1 0 5600
1 1 29025
|
Заключительный шаг – использовать выражение CASE для определения категории строки на основании значений, возвращенных в результате конкатенации GROUPING(JOB) и GROUPING(DEPTNO):
select deptno,
job,
case grouping(deptno)||grouping(job)
when '00' then 'TOTAL BY DEPT AND JOB'
when '10' then 'TOTAL BY JOB'
when '01' then 'TOTAL BY DEPT'
when '11' then 'GRAND TOTAL FOR TABLE'
end category,
sum(sal) sal
from emp
group by cube(deptno,job)
order by grouping(job),grouping(deptno)
DEPTNO JOB CATEGORY SAL
10 CLERK TOTAL BY DEPT AND JOB 1300
10 MANAGER TOTAL BY DEPT AND JOB 2450
10 PRESIDENT TOTAL BY DEPT AND JOB 5000
20 CLERK TOTAL BY DEPT AND JOB 1900
30 CLERK TOTAL BY DEPT AND JOB 950
30 SALESMAN TOTAL BY DEPT AND JOB 5600
30 MANAGER TOTAL BY DEPT AND JOB 2850
20 MANAGER TOTAL BY DEPT AND JOB 2975
20 ANALYST TOTAL BY DEPT AND JOB 6000
CLERK TOTAL BY JOB 4150
ANALYST TOTAL BY JOB 6000
MANAGER TOTAL BY JOB 8275
PRESIDENT TOTAL BY JOB 5000
SALESMAN TOTAL BY JOB 5600
10 TOTAL BY DEPT 8750
30 TOTAL BY DEPT 9400
20 TOTAL BY DEPT 10875
GRAND TOTAL FOR TABLE 29025
|
В этом решении для Oracle при подготовке к конкатенации результаты, возвращаемые функциями GROUPING, неявно преобразуются в символьный тип данных. Пользователям DB2 и SQL Server придется явно приводить (используя функцию CAST) результаты функций GROUPING к типу CHAR(1), как показано в решении. Кроме того, в SQL Server для объединения результатов двух вызовов GROUPING в одну строку используется оператор конкатенации +, а не ||.
Пользователям Oracle и DB2 доступно дополнительное исключительно полезное расширение GROUP BY под названием GROUPING SETS. С помощью GROUPING SETS можно, например, имитировать вывод, создаваемый CUBE, как это сделано ниже (пользователям DB2 и SQL Server потребуется применить к значениям, возвращаемым функцией GROUPING, явные операторы CAST, как в решении с расширением CUBE):
select deptno,
job,
case grouping(deptno)||grouping(job)
when '00' then 'TOTAL BY DEPT AND JOB'
when '10' then 'TOTAL BY JOB'
when '01' then 'TOTAL BY DEPT'
when '11' then 'GRAND TOTAL FOR TABLE'
end category,
sum(sal) sal
from emp
group by grouping sets ((deptno),(job),(deptno,job),())
DEPTNO JOB CATEGORY SAL
10 CLERK TOTAL BY DEPT AND JOB 1300
20 CLERK TOTAL BY DEPT AND JOB 1900
30 CLERK TOTAL BY DEPT AND JOB 950
20 ANALYST TOTAL BY DEPT AND JOB 6000
10 MANAGER TOTAL BY DEPT AND JOB 2450
20 MANAGER TOTAL BY DEPT AND JOB 2975
30 MANAGER TOTAL BY DEPT AND JOB 2850
30 SALESMAN TOTAL BY DEPT AND JOB 5600
10 PRESIDENT TOTAL BY DEPT AND JOB 5000
CLERK TOTAL BY JOB 4150
ANALYST TOTAL BY JOB 6000
MANAGER TOTAL BY JOB 8275
SALESMAN TOTAL BY JOB 5600
PRESIDENT TOTAL BY JOB 5000
10 TOTAL BY DEPT 8750
20 TOTAL BY DEPT 10875
30 TOTAL BY DEPT 9400
GRAND TOTAL FOR TABLE 29025
|
GROUPING SETS замечателен тем, что позволяет задавать группы. Оператор GROUPING SETS в предыдущем запросе обусловливает создание групп по DEPTNO, по JOB, по сочетанию DEPTNO и JOB, и, наконец, пустые круглые скобки соответствуют запросу на получение общей суммы. GROUPING SETS обеспечивает колоссальную гибкость для создания отчетов с разными уровнями агрегации. Например, чтобы в предыдущем примере исключить из результирующего множества общую сумму (GRAND TOTAL), надо просто убрать из списка оператора GROUPING SETS пустые круглые скобки:
select deptno,
job,
case grouping(deptno)||grouping(job)
when '00' then 'TOTAL BY DEPT AND JOB'
when '10' then 'TOTAL BY JOB'
when '01' then 'TOTAL BY DEPT'
when '11' then 'GRAND TOTAL FOR TABLE'
end category,
sum(sal) sal
from emp
group by grouping sets ((deptno),(job),(deptno,job))
DEPTNO JOB CATEGORY SAL
10 CLERK TOTAL BY DEPT AND JOB 1300
20 CLERK TOTAL BY DEPT AND JOB 1900
30 CLERK TOTAL BY DEPT AND JOB 950
20 ANALYST TOTAL BY DEPT AND JOB 6000
10 MANAGER TOTAL BY DEPT AND JOB 2450
20 MANAGER TOTAL BY DEPT AND JOB 2975
30 MANAGER TOTAL BY DEPT AND JOB 2850
30 SALESMAN TOTAL BY DEPT AND JOB 5600
10 PRESIDENT TOTAL BY DEPT AND JOB 5000
CLERK TOTAL BY JOB 4150
ANALYST TOTAL BY JOB 6000
MANAGER TOTAL BY JOB 8275
SALESMAN TOTAL BY JOB 5600
PRESIDENT TOTAL BY JOB 5000
10 TOTAL BY DEPT 8750
20 TOTAL BY DEPT 10875
30 TOTAL BY DEPT 9400
|
Можно также убрать какую-то из подсумм, например по DEPTNO, просто опуская (DEPTNO) в списке GROUPING SETS:
select deptno,
job,
case grouping(deptno)||grouping(job)
when '00' then 'TOTAL BY DEPT AND JOB'
when '10' then 'TOTAL BY JOB'
when '01' then 'TOTAL BY DEPT'
when '11' then 'GRAND TOTAL FOR TABLE'
end category,
sum(sal) sal
from emp
group by grouping sets ((job),(deptno,job),())
order by 3
DEPTNO JOB CATEGORY SAL
GRAND TOTAL FOR TABLE 29025
10 CLERK TOTAL BY DEPT AND JOB 1300
20 CLERK TOTAL BY DEPT AND JOB 1900
30 CLERK TOTAL BY DEPT AND JOB 950
20 ANALYST TOTAL BY DEPT AND JOB 6000
20 MANAGER TOTAL BY DEPT AND JOB 2975
30 MANAGER TOTAL BY DEPT AND JOB 2850
30 SALESMAN TOTAL BY DEPT AND JOB 5600
10 PRESIDENT TOTAL BY DEPT AND JOB 5000
10 MANAGER TOTAL BY DEPT AND JOB 2450
CLERK TOTAL BY JOB 4150
SALESMAN TOTAL BY JOB 5600
PRESIDENT TOTAL BY JOB 5000
MANAGER TOTAL BY JOB 8275
ANALYST TOTAL BY JOB 6000
|
Как видите, с GROUPING SETS очень просто манипулировать суммами и подсуммами, представляя данные в разных ракурсах.
PostgreSQL и MySQL
Первый шаг – использовать агрегатную функцию SUM и группировать значения по DEPTNO и JOB:
select deptno, job,
'TOTAL BY DEPT AND JOB' as category,
sum(sal) as sal
from emp
group by deptno, job
DEPTNO JOB CATEGORY SAL
10 CLERK TOTAL BY DEPT AND JOB 1300
10 MANAGER TOTAL BY DEPT AND JOB 2450
10 PRESIDENT TOTAL BY DEPT AND JOB 5000
20 CLERK TOTAL BY DEPT AND JOB 1900
20 ANALYST TOTAL BY DEPT AND JOB 6000
20 MANAGER TOTAL BY DEPT AND JOB 2975
30 CLERK TOTAL BY DEPT AND JOB 950
30 MANAGER TOTAL BY DEPT AND JOB 2850
30 SALESMAN TOTAL BY DEPT AND JOB 5600
|
Далее с помощью UNION ALL находим суммы всех заработных плат по должностям (JOB):
select deptno, job,
'TOTAL BY DEPT AND JOB' as category,
sum(sal) as sal
from emp
group by deptno, job
union all
select null, job, 'TOTAL BY JOB', sum(sal)
from emp
group by job
DEPTNO JOB CATEGORY SAL
10 CLERK TOTAL BY DEPT AND JOB 1300
10 MANAGER TOTAL BY DEPT AND JOB 2450
10 PRESIDENT TOTAL BY DEPT AND JOB 5000
20 CLERK TOTAL BY DEPT AND JOB 1900
20 ANALYST TOTAL BY DEPT AND JOB 6000
20 MANAGER TOTAL BY DEPT AND JOB 2975
30 CLERK TOTAL BY DEPT AND JOB 950
30 MANAGER TOTAL BY DEPT AND JOB 2850
30 SALESMAN TOTAL BY DEPT AND JOB 5600
ANALYST TOTAL BY JOB 6000
CLERK TOTAL BY JOB 4150
MANAGER TOTAL BY JOB 8275
PRESIDENT TOTAL BY JOB 5000
SALESMAN TOTAL BY JOB 5600
|
Следующий шаг – с помощью UNION ALL находим суммы всех заработных плат по отделам (DEPTNO):
select deptno, job,
'TOTAL BY DEPT AND JOB' as category,
sum(sal) as sal
from emp
group by deptno, job
union all
select null, job, 'TOTAL BY JOB', sum(sal)
from emp
group by job
union all
select deptno, null, 'TOTAL BY DEPT', sum(sal)
from emp
group by deptno
DEPTNO JOB CATEGORY SAL
10 CLERK TOTAL BY DEPT AND JOB 1300
10 MANAGER TOTAL BY DEPT AND JOB 2450
10 PRESIDENT TOTAL BY DEPT AND JOB 5000
20 CLERK TOTAL BY DEPT AND JOB 1900
20 ANALYST TOTAL BY DEPT AND JOB 6000
20 MANAGER TOTAL BY DEPT AND JOB 2975
30 CLERK TOTAL BY DEPT AND JOB 950
30 MANAGER TOTAL BY DEPT AND JOB 2850
30 SALESMAN TOTAL BY DEPT AND JOB 5600
ANALYST TOTAL BY JOB 6000
CLERK TOTAL BY JOB 4150
MANAGER TOTAL BY JOB 8275
PRESIDENT TOTAL BY JOB 5000
SALESMAN TOTAL BY JOB 5600
10 TOTAL BY DEPT 8750
20 TOTAL BY DEPT 10875
30 TOTAL BY DEPT 9400
|
Заключительный шаг – посредством UNION ALL вычисляем сумму всех заработных плат таблицы EMP:
select deptno, job,
'TOTAL BY DEPT AND JOB' as category,
sum(sal) as sal
from emp
group by deptno, job
union all
select null, job, 'TOTAL BY JOB', sum(sal)
from emp
group by job
union all
select deptno, null, 'TOTAL BY DEPT', sum(sal)
from emp
group by deptno
union all
select null,null, 'GRAND TOTAL FOR TABLE', sum(sal)
from emp
DEPTNO JOB CATEGORY SAL
10 CLERK TOTAL BY DEPT AND JOB 1300
10 MANAGER TOTAL BY DEPT AND JOB 2450
10 PRESIDENT TOTAL BY DEPT AND JOB 5000
20 CLERK TOTAL BY DEPT AND JOB 1900
20 ANALYST TOTAL BY DEPT AND JOB 6000
20 MANAGER TOTAL BY DEPT AND JOB 2975
30 CLERK TOTAL BY DEPT AND JOB 950
30 MANAGER TOTAL BY DEPT AND JOB 2850
30 SALESMAN TOTAL BY DEPT AND JOB 5600
ANALYST TOTAL BY JOB 6000
CLERK TOTAL BY JOB 4150
MANAGER TOTAL BY JOB 8275
PRESIDENT TOTAL BY JOB 5000
SALESMAN TOTAL BY JOB 5600
10 TOTAL BY DEPT 8750
20 TOTAL BY DEPT 10875
30 TOTAL BY DEPT 9400
GRAND TOTAL FOR TABLE 29025
|
Как выявить строки, в которых представлены не подсуммы
Задача
При создании отчета использовалось расширение CUBE оператора GROUP BY. Как отличить строки, сформированные обычным оператором GROUP BY, и строки, являющиеся результатом выполнения CUBE или ROLLUP?
Ниже представлено результирующее множество, возвращаемое запросом, в котором для анализа заработных плат таблицы EMP используется расширение CUBE оператора GROUP BY:
DEPTNO JOB SAL
------ --------- -----
29025
CLERK 4150
ANALYST 6000
MANAGER 8275
SALESMAN 5600
PRESIDENT 5000
10 8750
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 10875
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
30 9400
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
|
Этот отчет включает сумму всех заработных плат по DEPTNO и JOB (для каждой должности по отделам), сумму всех заработных плат по DEPTNO (по отделам), сумму всех заработных плат по JOB (по должностям) и, наконец, общую сумму заработных плат (сумму всех заработных плат таблицы EMP). Уровни агрегации должны быть четко обозначены. Для каждого значения должно быть точно определено, к какой категории оно относится (т. е. представляет ли данное значение столбца SAL сумму по DEPTNO? По JOB? Общую сумму?) Требуется получить следующее результирующее множество:
DEPTNO JOB SAL DEPTNO_SUBTOTALS JOB_SUBTOTALS
29025 1 1
CLERK 4150 1 0
ANALYST 6000 1 0
MANAGER 8275 1 0
SALESMAN 5600 1 0
PRESIDENT 5000 1 0
10 8750 0 1
10 CLERK 1300 0 0
10 MANAGER 2450 0 0
10 PRESIDENT 5000 0 0
20 10875 0 1
20 CLERK 1900 0 0
20 ANALYST 6000 0 0
20 MANAGER 2975 0 0
30 9400 0 1
30 CLERK 950 0 0
30 MANAGER 2850 0 0
30 SALESMAN 5600 0 0
|
Решение
Чтобы обозначить, какие из значений получены благодаря выполнению CUBE или ROLLUP, а какие из них являются суперагрегатными, используйте функцию GROUPING. Ниже представлен пример для DB2 и Oracle:
1 select deptno, job, sum(sal) sal,
2 grouping(deptno) deptno_subtotals,
3 grouping(job) job_subtotals
4 from emp
5 group by cube(deptno,job)
|
Решение для SQL Server отличается от решения для DB2 и Oracle только записью операторов CUBE/ROLLUP:
1 select deptno, job, sum(sal) sal,
2 grouping(deptno) deptno_subtotals,
3 grouping(job) job_subtotals
4 from emp
5 group by deptno,job with cube
|
Этот рецепт посвящен использованию CUBE и GROUPING при работе с подсуммами. На момент написания данной книги PostgreSQL и MySQL не поддерживают ни CUBE, ни GROUPING.
Обсуждение
Если DEPTNO_SUBTOTALS равно 1, то значение поля SAL представляет подсумму по DEPTNO, созданную CUBE. Если JOB_SUBTOTALS равно 1, то значение поля SAL представляет подсумму по JOB, созданную CUBE. Если в обоих столбцах, JOB_SUBTOTALS и DEPTNO_SUBTOTALS, располагается 1, значение SAL представляет общую сумму всех заработных плат, сформированную CUBE. Строки, в которых оба значения, DEPTNO_SUBTOTALS и JOB_SUBTOTALS, равны 0, созданы в результате обычной агрегации (значение SAL представляет сумму заработных плат для каждого сочетания DEPTNO/JOB).
Использование выражений CASE для маркировки строк
Задача
Требуется представить значения столбца, скажем, столбца JOB таблицы EMP, в виде набора «логических» флагов. Например, необходимо получить следующее результирующее множество:
ENAME IS_CLERK IS_SALES IS_MGR IS_ANALYST IS_PREZ
KING 0 0 0 0 1
SCOTT 0 0 0 1 0
FORD 0 0 0 1 0
JONES 0 0 1 0 0
BLAKE 0 0 1 0 0
CLARK 0 0 1 0 0
ALLEN 0 1 0 0 0
WARD 0 1 0 0 0
MARTIN 0 1 0 0 0
TURNER 0 1 0 0 0
SMITH 1 0 0 0 0
MILLER 1 0 0 0 0
ADAMS 1 0 0 0 0
JAMES 1 0 0 0 0
|
Подобное результирующее множество может использоваться для отладки и для представления данных иначе, чем предлагают обычные результирующие множества.
Решение
С помощью выражения CASE для каждого служащего определите должность (JOB) и возвратите 1 или 0, чтобы обозначить ее. Потребуется написать выражение CASE и, таким образом, создать столбец для каждой из возможных должностей:
1 select ename,
2 case when job = 'CLERK'
3 then 1 else 0
4 end as is_clerk,
5 case when job = 'SALESMAN'
6 then 1 else 0
7 end as is_sales,
8 case when job = 'MANAGER'
9 then 1 else 0
10 end as is_mgr,
11 case when job = 'ANALYST'
12 then 1 else 0
13 end as is_analyst,
14 case when job = 'PRESIDENT'
15 then 1 else 0
16 end as is_prez
17 from emp
18 order by 2,3,4,5,6
|
Обсуждение
Код данного решения практически не требует пояснений. Если возникли трудности с его пониманием, просто добавьте в оператор SELECT столбец JOB:
select ename,
job,
case when job = 'CLERK'
then 1 else 0
end as is_clerk,
case when job = 'SALESMAN'
then 1 else 0
end as is_sales,
case when job = 'MANAGER'
then 1 else 0
end as is_mgr,
case when job = 'ANALYST'
then 1 else 0
end as is_analyst,
case when job = 'PRESIDENT'
then 1 else 0
end as is_prez
from emp
order by 2
ENAME JOB IS_CLERK IS_SALES IS_MGR IS_ANALYST IS_PREZ
______ _________ ________ ________ ______ __________ _______
SCOTT ANALYST 0 0 0 1 0
FORD ANALYST 0 0 0 1 0
SMITH CLERK 1 0 0 0 0
ADAMS CLERK 1 0 0 0 0
MILLER CLERK 1 0 0 0 0
JAMES CLERK 1 0 0 0 0
JONES MANAGER 0 0 1 0 0
CLARK MANAGER 0 0 1 0 0
BLAKE MANAGER 0 0 1 0 0
KING PRESIDENT 0 0 0 0 1
ALLEN SALESMAN 0 1 0 0 0
MARTIN SALESMAN 0 1 0 0 0
TURNER SALESMAN 0 1 0 0 0
WARD SALESMAN 0 1 0 0 0
|
Создание разреженной матрицы
Задача
Требуется создать разреженную матрицу, такую как представлена ниже, в которой транспонированы столбцы DEPTNO и JOB таблицы EMP:
D10 D20 D30 CLERKS MGRS PREZ ANALS SALES
__________ __________ __________ ______ _____ ____ _____ ______
SMITH SMITH
ALLEN ALLEN
WARD WARD
JONES JONES
MARTIN MARTIN
BLAKE BLAKE
CLARK CLARK
SCOTT SCOTT
KING KING
TURNER TURNER
ADAMS ADAMS
JAMES JAMES
FORD FORD
MILLER MILLER
|
Решение
Для создания разреженной таблицы, в которой строки транспонированы в столбцы, используйте выражения CASE:
1 select case deptno when 10 then ename end as d10,
2 case deptno when 20 then ename end as d20,
3 case deptno when 30 then ename end as d30,
4 case job when 'CLERK' then ename end as clerks,
5 case job when 'MANAGER' then ename end as mgrs,
6 case job when 'PRESIDENT' then ename end as prez,
7 case job when 'ANALYST' then ename end as anals,
8 case job when 'SALESMAN' then ename end as sales
9 from emp
|
Обсуждение
Чтобы превратить строки DEPTNO и JOB в столбцы, просто используем выражение CASE, обрабатывающее значения, которые могут быть возвращены в этих строках. Вот и все. В дополнение к этому, если требуется «уплотнить» отчет и избавиться от строк со значениями NULL, необходимо определиться с принципом группировки. Например, с помощью ранжирующей функции ROW_NUMBER OVER присвойте ранги всем служащим, разделяя их по DEPTNO, и затем, применяя агрегатную функцию MAX, удалите некоторые значения NULL:
select max(case deptno when 10 then ename end) d10,
max(case deptno when 20 then ename end) d20,
max(case deptno when 30 then ename end) d30,
max(case job when 'CLERK' then ename end) clerks,
max(case job when 'MANAGER' then ename end) mgrs,
max(case job when 'PRESIDENT' then ename end) prez,
max(case job when 'ANALYST' then ename end) anals,
max(case job when 'SALESMAN' then ename end) sales
from (
select deptno, job, ename,
row_number()over(partition by deptno order by empno) rn
from emp
) x
group by rn
D10 D20 D30 CLERKS MGRS PREZ ANALS SALES
__________ __________ __________ ______ _____ ____ _____ ______
CLARK SMITH ALLEN SMITH CLARK ALLEN
KING JONES WARD JONES KING WARD
MILLER SCOTT MARTIN MILLER SCOTT MARTIN
ADAMS BLAKE ADAMS BLAKE
FORD TURNER FORD TURNER
JAMES JAMES
|
Группировка строк по интервалам времени
Задача
Требуется обобщить данные по некоторому интервалу времени. Например, имеется журнал транзакций. Необходимо разбить период наблюдений на 5секундные интервалы и показать, сколько транзакций имело место в каждый из этих интервалов. Строки таблицы TRX_LOG показаны ниже:
select trx_id,
trx_date,
trx_cnt
from trx_log
TRX_ID TRX_DATE TRX_CNT
______ ____________________ __________
1 28_JUL_2005 19:03:07 44
2 28_JUL_2005 19:03:08 18
3 28_JUL_2005 19:03:09 23
4 28_JUL_2005 19:03:10 29
5 28_JUL_2005 19:03:11 27
6 28_JUL_2005 19:03:12 45
7 28_JUL_2005 19:03:13 45
8 28_JUL_2005 19:03:14 32
9 28_JUL_2005 19:03:15 41
10 28_JUL_2005 19:03:16 15
11 28_JUL_2005 19:03:17 24
12 28_JUL_2005 19:03:18 47
13 28_JUL_2005 19:03:19 37
14 28_JUL_2005 19:03:20 48
15 28_JUL_2005 19:03:21 46
16 28_JUL_2005 19:03:22 44
17 28_JUL_2005 19:03:23 36
18 28_JUL_2005 19:03:24 41
19 28_JUL_2005 19:03:25 33
20 28_JUL_2005 19:03:26 19
|
Должно быть получено следующее результирующее множество:
GRP TRX_START TRX_END TOTAL
___ ____________________ ____________________ __________
1 28_JUL_2005 19:03:07 28_JUL_2005 19:03:11 141
2 28_JUL_2005 19:03:12 28_JUL_2005 19:03:16 178
3 28_JUL_2005 19:03:17 28_JUL_2005 19:03:21 202
4 28_JUL_2005 19:03:22 28_JUL_2005 19:03:26 173
|
Решение
Группировать записи в блоки по пять строк. Такую логическую группировку можно реализовать несколькими способами. В данном рецепте она осуществляется путем деления значений TRX_ID на 5, т. е. используется техника, представленная ранее в разделе «Создание блоков данных фиксированного размера».
Когда «группы» установлены, с помощью агрегатных функций MIN, MAX и SUM определяются начальное время, конечное время и общее количество транзакций в каждой «группе» (для SQL Server используется функция CEILING, а не CEIL):
1 select ceil(trx_id/5.0) as grp,
2 min(trx_date) as trx_start,
3 max(trx_date) as trx_end,
4 sum(trx_cnt) as total
5 from trx_log
6 group by ceil(trx_id/5.0)
|
Обсуждение
Первый и ключевой для всего решения шаг – сгруппировать строки. Сформировать логические группы можно путем деления уникальных идентификаторов записей на 5 и возвращения наименьшего целого числа, которое больше, чем остаток от деления. Например:
select trx_id, trx_date, trx_cnt, trx_id/5.0 as val, ceil(trx_id/5.0) as grp
from trx_log
TRX_ID TRX_DATE TRX_CNT VAL GRP
______ ____________________ _______ ______ ___
1 28 JUL 2005 19:03:07 44 .20 1
2 28 JUL 2005 19:03:08 18 .40 1
3 28 JUL 2005 19:03:09 23 .60 1
4 28 JUL 2005 19:03:10 29 .80 1
5 28 JUL 2005 19:03:11 27 1.00 1
6 28 JUL 2005 19:03:12 45 1.20 2
7 28 JUL 2005 19:03:13 45 1.40 2
8 28 JUL 2005 19:03:14 32 1.60 2
9 28 JUL 2005 19:03:15 41 1.80 2
10 28 JUL 2005 19:03:16 15 2.00 2
11 28 JUL 2005 19:03:17 24 2.20 3
12 28 JUL 2005 19:03:18 47 2.40 3
13 28 JUL 2005 19:03:19 37 2.60 3
14 28 JUL 2005 19:03:20 48 2.80 3
15 28 JUL 2005 19:03:21 46 3.00 3
16 28 JUL 2005 19:03:22 44 3.20 4
17 28 JUL 2005 19:03:23 36 3.40 4
18 28 JUL 2005 19:03:24 41 3.60 4
19 28 JUL 2005 19:03:25 33 3.80 4
20 28 JUL 2005 19:03:26 19 4.00 4
|
Последний шаг – применить соответствующие агрегатные функции и найти, сколько транзакций произошло в течение каждого пятисекундного интервала, а также время начала и завершения каждой транзакции:
select ceil(trx_id/5.0) as grp,
min(trx_date) as trx_start,
max(trx_date) as trx_end,
sum(trx_cnt) as total
from trx_log
group by ceil(trx_id/5.0)
GRP TRX_START TRX_END TOTAL
___ ____________________ ____________________ __________
1 28_JUL_2005 19:03:07 28_JUL_2005 19:03:11 141
2 28_JUL_2005 19:03:12 28_JUL_2005 19:03:16 178
3 28_JUL_2005 19:03:17 28_JUL_2005 19:03:21 202
4 28_JUL_2005 19:03:22 28_JUL_2005 19:03:26 173
|
Если ваши данные немного отличаются от рассматриваемых (скажем, строки не имеют ID), всегда можно создать подобные группы путем деления секунд из значений TRX_DATE на 5. Затем для каждого значения TRX_DATE включаем часы и группируем по фактическому часу и логической «группе», GRP. Ниже представлен пример реализации такой техники (фигурирующие здесь функции Oracle TO_CHAR и TO_ NUMBER необходимо заменить на функции работы с датами и форматирования символов, соответствующие используемой платформе):
select trx_date,trx_cnt,
to_number(to_char(trx_date,'hh24')) hr,
ceil(to_number(to_char(trx_date-1/24/60/60,'miss'))/5.0) grp
from trx_log
TRX_DATE TRX_CNT HR GRP
-------------------- ------- --- ---
28 JUL 2005 19:03:07 44 19 62
28 JUL 2005 19:03:08 18 19 62
28 JUL 2005 19:03:09 23 19 62
28 JUL 2005 19:03:10 29 19 62
28 JUL 2005 19:03:11 27 19 62
28 JUL 2005 19:03:12 45 19 63
28 JUL 2005 19:03:13 45 19 63
28 JUL 2005 19:03:14 32 19 63
28 JUL 2005 19:03:15 41 19 63
28 JUL 2005 19:03:16 15 19 63
28 JUL 2005 19:03:17 24 19 64
28 JUL 2005 19:03:18 47 19 64
28 JUL 2005 19:03:19 37 19 64
28 JUL 2005 19:03:20 48 19 64
28 JUL 2005 19:03:21 46 19 64
28 JUL 2005 19:03:22 44 19 65
28 JUL 2005 19:03:23 36 19 65
28 JUL 2005 19:03:24 41 19 65
28 JUL 2005 19:03:25 33 19 65
28 JUL 2005 19:03:26 19 19 65
|
Суть здесь в том, что группировка осуществляется для каждых 5 секунд независимо от фактических значений GRP. После этого уже можно применять агрегатные функции, так же как и в исходном решении:
select hr,grp,sum(trx_cnt) total
from (
select trx_date,trx_cnt,
to_number(to_char(trx_date,'hh24')) hr,
ceil(to_number(to_char(trx_date-1/24/60/60,'miss'))/5.0) grp
from trx_log
) x
group by hr,grp
HR GRP TOTAL
-- --- -----
19 62 141
19 63 178
19 64 202
19 65 173
|
Группировать транзакции по часу можно, если журнал транзакций охватывает большие промежутки времени. В DB2 и Oracle такой же результат можно получить с помощью оконной функции SUM OVER. Следующий запрос возвращает все строки таблицы TRX_LOG и, логически их группируя, вычисляет промежуточные суммы (столбец TRX_ CNT) и общую сумму транзакций TOTAL (столбец TRX_CNT) в каждой строке «группы»:
select trx_id, trx_date, trx_cnt,
sum(trx_cnt)over(partition by ceil(trx_id/5.0)
order by trx_date
range between unbounded preceding
and current row) runing_total,
sum(trx_cnt)over(partition by ceil(trx_id/5.0)) total,
case when mod(trx_id,5.0) = 0 then 'X' end grp_end
from trx_log
TRX_ID TRX_DATE TRX_CNT RUNING_TOTAL TOTAL GRP_END
______ ____________________ __________ ____________ __________ _______
1 28_JUL_2005 19:03:07 44 44 141
2 28_JUL_2005 19:03:08 18 62 141
3 28_JUL_2005 19:03:09 23 85 141
4 28_JUL_2005 19:03:10 29 114 141
5 28_JUL_2005 19:03:11 27 141 141 X
6 28_JUL_2005 19:03:12 45 45 178
7 28_JUL_2005 19:03:13 45 90 178
8 28_JUL_2005 19:03:14 32 122 178
9 28_JUL_2005 19:03:15 41 163 178
10 28_JUL_2005 19:03:16 15 178 178 X
11 28_JUL_2005 19:03:17 24 24 202
12 28_JUL_2005 19:03:18 47 71 202
13 28_JUL_2005 19:03:19 37 108 202
14 28_JUL_2005 19:03:20 48 156 202
15 28_JUL_2005 19:03:21 46 202 202 X
16 28_JUL_2005 19:03:22 44 44 173
17 28_JUL_2005 19:03:23 36 80 173
18 28_JUL_2005 19:03:24 41 121 173
19 28_JUL_2005 19:03:25 33 154 173
20 28_JUL_2005 19:03:26 19 173 173 X
|
Агрегация разных групп/сегментов одновременно
Задача
Требуется осуществить агрегацию «в разных измерениях» одновременно. Например, необходимо получить результирующее множество, в котором для каждого сотрудника перечислены имя, отдел, количество служащих в отделе (включая его самого), количество служащих, занимающих ту же должность, что и он (также включая его самого), и общее число служащих в таблице EMP. Таким образом, результирующее множество должно иметь следующий вид:
ENAME DEPTNO DEPTNO_CNT JOB JOB_CNT TOTAL
______ ______ __________ _________ ________ ______
MILLER 10 3 CLERK 4 14
CLARK 10 3 MANAGER 3 14
KING 10 3 PRESIDENT 1 14
SCOTT 20 5 ANALYST 2 14
FORD 20 5 ANALYST 2 14
SMITH 20 5 CLERK 4 14
JONES 20 5 MANAGER 3 14
ADAMS 20 5 CLERK 4 14
JAMES 30 6 CLERK 4 14
MARTIN 30 6 SALESMAN 4 14
TURNER 30 6 SALESMAN 4 14
WARD 30 6 SALESMAN 4 14
ALLEN 30 6 SALESMAN 4 14
BLAKE 30 6 MANAGER 3 14
|
Оконные функции упрощают решение этой задачи. Если в вашем распоряжении нет оконных функций, можно использовать скалярные подзапросы.
DB2, Oracle и SQL Server
Используйте оконную функцию COUNT OVER, задавая разные сегменты или группы данных, для которых проводится агрегация:
select ename,
deptno,
count(*)over(partition by deptno) deptno_cnt,
job,
count(*)over(partition by job) job_cnt,
count(*)over() total
from emp
|
PostgreSQL и MySQL
Для выполнения операций агрегации COUNT разных групп строк используйте скалярные подзапросы в списке оператора SELECT:
1 select e.ename,
2 e.deptno,
3 (select count(*) from emp d
4 where d.deptno = e.deptno) as deptno_cnt,
5 job,
6 (select count(*) from emp d
7 where d.job = e.job) as job_cnt,
8 (select count(*) from emp) as total
9 from emp e
|
Обсуждение
DB2, Oracle и SQL Server
Данный пример действительно показывает мощь и преимущества оконных функций. Всего лишь задавая различные сегменты или группы данных, подлежащих агрегации, можно создавать чрезвычайно подробные отчеты без бесконечных рефлексивных объединений и без громоздких и, возможно, низкопроизводительных подзапросов в списке SELECT. Всю работу выполняет оконная функция COUNT OVER. Чтобы понять полученный результат, остановимся на операторе OVER каждой операции COUNT:
count(*)over(partition by deptno)
count(*)over(partition by job)
count(*)over()
|
Вспомним основные части оператора OVER: сегмент, определяемый ключевым словом PARTITION BY, и кадр или окно данных, определяемое ORDER BY. Посмотрим на первый оператор COUNT, в котором задано сегментирование по DEPTNO. Строки таблицы EMP будут сгруппированы по DEPTNO, и операция COUNT будет выполнена над всеми строками каждой группы. Поскольку кадр или окно данных не определено (нет оператора ORDER BY), пересчитываются все строки группы. Оператор PARTITION BY находит все уникальные значения DEPTNO, для каждого из них функция COUNT подсчитывает количество строк, имеющих это значение. В конкретном примере COUNT(*)OVER(PARTITION BY DEPTNO) оператор PARTITION BY выделяет сегменты или группы по значениям 10, 20 и 30.
То же самое происходит для второй функции COUNT с сегментированием по JOB. В последней COUNT сегменты не определены, просто указаны пустые круглые скобки. Пустые круглые скобки подразумевают «всю таблицу». Таким образом, тогда как две предыдущие операции COUNT обрабатывают заданные группы или сегменты данных, последняя COUNT подсчитывает все строки таблицы EMP.
Не забывайте, что оконные функции выполняются после предиката WHERE. Если бы вы применили к результирующему множеству некоторый фильтр, например исключающий всех служащих 10го отдела (DEPTNO 10), значение TOTAL было бы не 14, а 11. Чтобы фильтровать результаты после выполнения оконных функций, необходимо поместить запрос с функцией во вложенный запрос и затем фильтровать результаты, возвращенные этим запросом.
PostgreSQL и MySQL
Для проведения различных подсчетов для каждого отдела и должности используйте несколько скалярных подзапросов в списке SELECT, обрабатывая каждую строку, возвращаемую основным запросом (строки из EMP E). Чтобы получить значение TOTAL, просто с помощью другого скалярного подзапроса пересчитайте всех служащих таблицы EMP.
Агрегация скользящего множества значений
Задача
Требуется выполнить скользящую агрегацию, например, найти скользящую сумму заработных плат таблицы EMP. Будем вычислять сумму для каждого интервала в 90 дней, начиная с даты приема на работу (HIREDATE) первого служащего, чтобы увидеть динамику изменения расходов для каждого 90дневного периода между датами приема на работу первого и последнего служащих. Должно быть получено следующее результирующее множество:
HIREDATE SAL SPENDING_PATTERN
___________ _______ ________________
17_DEC_1980 800 800
20_FEB_1981 1600 2400
22_FEB_1981 1250 3650
02_APR_1981 2975 5825
01_MAY_1981 2850 8675
09_JUN_1981 2450 8275
08_SEP_1981 1500 1500
28_SEP_1981 1250 2750
17_NOV_1981 5000 7750
03_DEC_1981 950 11700
03_DEC_1981 3000 11700
23_JAN_1982 1300 10250
09_DEC_1982 3000 3000
12_JAN_1983 1100 4100
|
Решение
Возможность задавать скользящее окно в операторе сегментирования оконных функций сильно упрощает решение этой задачи, если используемая СУБД поддерживает такие функции. Ключ к решению – выполнить упорядочение по HIREDATE в оконной функции и затем задать окно в 90 дней, начиная с даты приема на работу первого служащего. В сумму войдут заработные платы служащих, принятых на работу в течение 90 дней до даты HIREDATE текущего служащего (зарплата текущего служащего включается в сумму). Если в распоряжении нет оконных функций, можно воспользоваться скалярными подзапросами, но тогда решение будет более сложным.
DB2 и Oracle
Для DB2 и Oracle используйте оконную функцию SUM OVER и сортировку по HIREDATE. В операторе сегментирования задайте диапазон 90 дней, чтобы в сумму были включены заработные платы всех служащих, принятых на работу в течение предыдущих 90 дней. Поскольку DB2 не позволяет задавать HIREDATE в операторе ORDER BY оконной функции (строка 3 в фрагменте кода ниже), можно сортировать по DAYS(HIREDATE):
1 select hiredate,
2 sal,
3 sum(sal)over(order by days(hiredate)
4 range between 90 preceding
5 and current row) spending_pattern
6 from emp e
|
Решение для Oracle более понятное, чем для DB2, потому что в Oracle в оконных функциях можно проводить сортировку по типам даты-времени:
1 select hiredate,
2 sal,
3 sum(sal)over(order by hiredate
4 range between 90 preceding
5 and current row) spending_pattern
6 from emp e
|
MySQL, PostgreSQL и SQL Server
Чтобы для каждого служащего просуммировать заработные платы сотрудников, принятых на работу в течение 90 дней до дня найма рассматриваемого сотрудника, используйте скалярный подзапрос:
1 select e.hiredate,
2 e.sal,
3 (select sum(sal) from emp d
4 where d.hiredate between e.hiredate 90
5 and e.hiredate) as spending_pattern
6 from emp e
7 order by 1
|
Обсуждение
DB2 и Oracle
Для DB2 и Oracle используется одно и то же решение. Единственное небольшое отличие в том, как задается HIREDATE в операторе ORDER BY оконной функции. На момент написания данной книги DB2 не допускает применения значений типа DATE в ORDER BY, если для определения окна данных используется числовое значение. (Например, если задано RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, сортировка по дате допускается, а если RANGE BETWEEN 90 PRECEDING AND CURRENT ROW – нет.)
Чтобы понять, что делает запрос, представленный в решении, необходимо просто разобраться, как работает оператор сегментирования. Задаваемое окно данных обусловливает упорядочение заработных плат всех служащих по HIREDATE. Затем функция вычисляет сумму. Сумма вычисляется не для всех заработных плат. Происходит следующее:
- Определяется заработная плата служащего, принятого на работу первым. Поскольку служащих, нанятых раньше него, нет, сумма в данной точке просто равна заработной плате первого служащего.
- Определяется заработная плата следующего (соответственно HIREDATE) служащего. Она включается в скользящую сумму вместе с заработными платами других сотрудников, принятых на работу в течение 90 дней до дня найма рассматриваемого сотрудника.
Дата HIREDATE первого служащего – 17 декабря 1980 года, а HIREDATE служащего, поступившего следующим, – 20 февраля 1981. Второй служащий был принят на работу раньше, чем через 90 дней после первого служащего, таким образом, скользящая сумма для второго сотрудника – 2400 (1600 + 800). Чтобы не было трудностей с пониманием, откуда берутся значения поля SPENDING_PATTERN, рассмотрим следующий запрос и результирующее множество:
select distinct
dense_rank()over(order by e.hiredate) window,
e.hiredate current_hiredate,
d.hiredate hiredate_within_90_days,
d.sal sals_used_for_sum
from emp e,
emp d
where d.hiredate between e.hiredate-90 and e.hiredate
WINDOW CURRENT_HIREDATE HIREDATE_WITHIN_90_DAYS SALS_USED_FOR_SUM
______ ________________ _______________________ _________________
1 17_DEC_1980 17_DEC_1980 800
2 20_FEB_1981 17_DEC_1980 800
2 20_FEB_1981 20_FEB_1981 1600
3 22_FEB_1981 17_DEC_1980 800
3 22_FEB_1981 20_FEB_1981 1600
3 22_FEB_1981 22_FEB_1981 1250
4 02_APR_1981 20_FEB_1981 1600
4 02_APR_1981 22_FEB_1981 1250
4 02_APR_1981 02_APR_1981 2975
5 01_MAY_1981 20_FEB_1981 1600
5 01_MAY_1981 22_FEB_1981 1250
5 01_MAY_1981 02_APR_1981 2975
5 01_MAY_1981 01_MAY_1981 2850
6 09_JUN_1981 02_APR_1981 2975
6 09_JUN_1981 01_MAY_1981 2850
6 09_JUN_1981 09_JUN_1981 2450
7 08_SEP_1981 08_SEP_1981 1500
8 28_SEP_1981 08_SEP_1981 1500
8 28_SEP_1981 28_SEP_1981 1250
9 17_NOV_1981 08_SEP_1981 1500
9 17_NOV_1981 28_SEP_1981 1250
9 17_NOV_1981 17_NOV_1981 5000
10 03_DEC_1981 08_SEP_1981 1500
10 03_DEC_1981 28_SEP_1981 1250
10 03_DEC_1981 17_NOV_1981 5000
10 03_DEC_1981 03_DEC_1981 950
10 03_DEC_1981 03_DEC_1981 3000
11 23_JAN_1982 17_NOV_1981 5000
11 23_JAN_1982 03_DEC_1981 950
11 23_JAN_1982 03_DEC_1981 3000
11 23_JAN_1982 23_JAN_1982 1300
12 09_DEC_1982 09_DEC_1982 3000
13 12_JAN_1983 09_DEC_1982 3000
13 12_JAN_1983 12_JAN_1983 1100
|
В каждой сумме участвуют только строки с одинаковым значением поля WINDOW. Возьмем, к примеру, WINDOW 3. При вычислении суммы для этого окна используются заработные платы 800, 1600 и 1250, в сумме дающие 3650. Если взглянуть на окончательное результирующее множество в разделе «Задача», мы увидим, что значение SPENDING_PATTERN для 22 февраля 1981 (WINDOW 3) равно 3650. Убедиться в том, что приведенное выше рефлексивное объединение обеспечивает выбор соответствующих заработных плат для заданных окон, можно, просто просуммировав значения SALS_USED_FOR_SUM и проведя группировку по CURRENT_DATE. Результаты должны быть аналогичны результирующему множеству, представленному в разделе «Задача» (без дублирующейся строки для 3 декабря 1981):
select current_hiredate,
sum(sals_used_for_sum) spending_pattern
from (
select distinct
dense_rank()over(order by e.hiredate) window,
e.hiredate current_hiredate,
d.hiredate hiredate_within_90_days,
d.sal sals_used_for_sum
from emp e,
emp d
where d.hiredate between e.hiredate-90 and e.hiredate
) x
group by current_hiredate
CURRENT_HIREDATE SPENDING_PATTERN
________________ ________________
17_DEC_1980 800
20_FEB_1981 2400
22_FEB_1981 3650
02_APR_1981 5825
01_MAY_1981 8675
09_JUN_1981 8275
08_SEP_1981 1500
28_SEP_1981 2750
17_NOV_1981 7750
03_DEC_1981 11700
23_JAN_1982 10250
09_DEC_1982 3000
12_JAN_1983 4100
|
MySQL, PostgreSQL и SQL Server
В этом решении сумма заработных плат для каждых 90 дней на основании значений HIREDATE вычисляется с помощью скалярного подзапроса (подойдет и рефлексивное объединение) с агрегатной функцией SUM. Если возникают затруднения с пониманием происходящего, просто трансформируйте решение в рефлексивное объединение и проверьте, какие строки будут участвовать в вычислениях. Рассмотрим результирующее множество ниже, которое аналогично результату, приведенному в разделе «Решение»:
select e.hiredate,
e.sal,
sum(d.sal) as spending_pattern
from emp e, emp d
where d.hiredate
between e.hiredate-90 and e.hiredate
group by e.hiredate,e.sal
order by 1
HIREDATE SAL SPENDING_PATTERN
___________ _____ ________________
17_DEC_1980 800 800
20_FEB_1981 1600 2400
22_FEB_1981 1250 3650
02_APR_1981 2975 5825
01_MAY_1981 2850 8675
09_JUN_1981 2450 8275
08_SEP_1981 1500 1500
28_SEP_1981 1250 2750
17_NOV_1981 5000 7750
03_DEC_1981 950 11700
03_DEC_1981 3000 11700
23_JAN_1982 1300 10250
09_DEC_1982 3000 3000
12_JAN_1983 1100 4100
|
Если до сих пор остаются вопросы, уберите агрегацию и начните с получения декартова произведения. Первый шаг – используя таблицу EMP, создать декартово произведение, чтобы каждое значение HIREDATE можно было сравнивать со всеми другими значениями HIREDATE. (Ниже показан лишь фрагмент результирующего множества, потому что декартово произведение таблицы EMP включает 196 строк (14?14)
select e.hiredate,
e.sal,
d.sal,
d.hiredate
from emp e, emp d
|
HIREDATE SAL SAL HIREDATE
___________ _____ _____ ___________
17_DEC_1980 800 800 17_DEC_1980
17_DEC_1980 800 1600 20_FEB_1981
17_DEC_1980 800 1250 22_FEB_1981
17_DEC_1980 800 2975 02_APR_1981
17_DEC_1980 800 1250 28_SEP_1981
17_DEC_1980 800 2850 01_MAY_1981
17_DEC_1980 800 2450 09_JUN_1981
17_DEC_1980 800 3000 09_DEC_1982
17_DEC_1980 800 5000 17_NOV_1981
17_DEC_1980 800 1500 08_SEP_1981
17_DEC_1980 800 1100 12_JAN_1983
17_DEC_1980 800 950 03_DEC_1981
17_DEC_1980 800 3000 03_DEC_1981
17_DEC_1980 800 1300 23_JAN_1982
20_FEB_1981 1600 800 17_DEC_1980
20_FEB_1981 1600 1600 20_FEB_1981
20_FEB_1981 1600 1250 22_FEB_1981
20_FEB_1981 1600 2975 02_APR_1981
20_FEB_1981 1600 1250 28_SEP_1981
20_FEB_1981 1600 2850 01_MAY_1981
20_FEB_1981 1600 2450 09_JUN_1981
20_FEB_1981 1600 3000 09_DEC_1982
20_FEB_1981 1600 5000 17_NOV_1981
20_FEB_1981 1600 1500 08_SEP_1981
20_FEB_1981 1600 1100 12_JAN_1983
20_FEB_1981 1600 950 03_DEC_1981
20_FEB_1981 1600 3000 03_DEC_1981
20_FEB_1981 1600 1300 23_JAN_1982
|
Если проанализировать это результирующее множество, можно заметить, что нет даты HIREDATE, на 90 дней раньше или соответствующей 17 декабря, кроме 17 декабря. Таким образом, сумма для данной строки должна составлять всего 800. Если посмотреть на следующую HIREDATE, 20 февраля, можно увидеть, что только одно значение HIREDATE попадает в 90дневное окно (предыдущие 90 дней), и это 17 декабря. Если сложить значения SAL для 17 декабря и 20 февраля (потому что мы ищем HIREDATE, равные рассматриваемой HIREDATE или попадающие в 90дневное окно до нее), получаем 2400, что является окончательным результатом для этой даты.
Разобравшись с тем, что происходит, применяем фильтр в предикате WHERE, чтобы получить результаты для каждой HIREDATE и HIREDATE, равной ей или попадающей в 90дневное окно до этой даты:
select e.hiredate,
e.sal,
d.sal sal_to_sum,
d.hiredate within_90_days
from emp e, emp d
where d.hiredate
between e.hiredate-90 and e.hiredate
order by 1
HIREDATE SAL SAL_TO_SUM WITHIN_90_DAYS
___________ _____ __________ ______________
17_DEC_1980 800 800 17_DEC_1980
20_FEB_1981 1600 800 17_DEC_1980
20_FEB_1981 1600 1600 20_FEB_1981
22_FEB_1981 1250 800 17_DEC_1980
22_FEB_1981 1250 1600 20_FEB_1981
22_FEB_1981 1250 1250 22_FEB_1981
02_APR_1981 2975 1600 20_FEB_1981
02_APR_1981 2975 1250 22_FEB_1981
02_APR_1981 2975 2975 02_APR_1981
01_MAY_1981 2850 1600 20_FEB_1981
01_MAY_1981 2850 1250 22_FEB_1981
01_MAY_1981 2850 2975 02_APR_1981
01_MAY_1981 2850 2850 01_MAY_1981
09_JUN_1981 2450 2975 02_APR_1981
09_JUN_1981 2450 2850 01_MAY_1981
09_JUN_1981 2450 2450 09_JUN_1981
08_SEP_1981 1500 1500 08_SEP_1981
28_SEP_1981 1250 1500 08_SEP_1981
28_SEP_1981 1250 1250 28_SEP_1981
17_NOV_1981 5000 1500 08_SEP_1981
17_NOV_1981 5000 1250 28_SEP_1981
17_NOV_1981 5000 5000 17_NOV_1981
03_DEC_1981 950 1500 08_SEP_1981
03_DEC_1981 950 1250 28_SEP_1981
03_DEC_1981 950 5000 17_NOV_1981
03_DEC_1981 950 950 03_DEC_1981
03_DEC_1981 950 3000 03_DEC_1981
03_DEC_1981 3000 1500 08_SEP_1981
03_DEC_1981 3000 1250 28_SEP_1981
03_DEC_1981 3000 5000 17_NOV_1981
03_DEC_1981 3000 950 03_DEC_1981
03_DEC_1981 3000 3000 03_DEC_1981
23_JAN_1982 1300 5000 17_NOV_1981
23_JAN_1982 1300 950 03_DEC_1981
23_JAN_1982 1300 3000 03_DEC_1981
23_JAN_1982 1300 1300 23_JAN_1982
09_DEC_1982 3000 3000 09_DEC_1982
12_JAN_1983 1100 3000 09_DEC_1982
12_JAN_1983 1100 1100 12_JAN_1983
|
Теперь, зная, какие значения SAL должны войти в скользящее окно для вычисления суммы, просто применяем агрегатную функцию SUM для получения более выразительного результирующего множества:
select e.hiredate,
e.sal,
sum(d.sal) as spending_pattern
from emp e, emp d
where d.hiredate
between e.hiredate 90 and e.hiredate
group by e.hiredate,e.sal
order by 1
|
Если сравнить результирующее множество приведенного выше запроса и результирующее множество следующего запроса (который является первоначально предлагаемым решением), мы увидим, что они абсолютно одинаковые:
select e.hiredate,
e.sal,
(select sum(sal) from emp d
where d.hiredate between e.hiredate 90
and e.hiredate) as spending_pattern
from emp e
order by 1
HIREDATE SAL SPENDING_PATTERN
___________ _____ ________________
17_DEC_1980 800 800
20_FEB_1981 1600 2400
22_FEB_1981 1250 3650
02_APR_1981 2975 5825
01_MAY_1981 2850 8675
09_JUN_1981 2450 8275
08_SEP_1981 1500 1500
28_SEP_1981 1250 2750
17_NOV_1981 5000 7750
03_DEC_1981 950 11700
03_DEC_1981 3000 11700
23_JAN_1982 1300 10250
09_DEC_1982 3000 3000
12_JAN_1983 1100 4100
|
Разворачивание результирующего множества, содержащего подсуммы
Задача
Требуется вычислить подсуммы, создать отчет и транспонировать его, чтобы обеспечить более наглядный результат. Например, поставлена задача создать отчет, представляющий руководителей каждого отдела и суммы заработных плат подчиненных каждого руководителя. Кроме того, необходимо получить две подсуммы: сумму всех заработных плат по отделам для служащих, работающих в чьем-то подчинении, и сумму всех заработных плат (сумму подсумм отдела). На данный момент имеется следующий отчет:
DEPTNO MGR SAL
______ __________ __________
10 7782 1300
10 7839 2450
10 3750
20 7566 6000
20 7788 1100
20 7839 2975
20 7902 800
20 10875
30 7698 6550
30 7839 2850
30 9400
24025
|
Необходимо сделать отчет более удобным для чтения и преобразовать приведенное выше результирующее множество в следующее:
MGR DEPT10 DEPT20 DEPT30 TOTAL
____ __________ __________ __________ __________
7566 0 6000 0
7698 0 0 6550
7782 1300 0 0
7788 0 1100 0
7839 2450 2975 2850
7902 0 800 0
3750 10875 9400 24025
|
Решение
Первый шаг – получить подсуммы, используя расширение ROLLUP оператора GROUP BY. Следующий шаг – выполнить классический разворот (с помощью агрегатной функции и выражения CASE) для создания необходимых столбцов отчета. Функция GROUPING обеспечивает возможность без труда определять значения, являющиеся подсуммами (т. е. полученные в результате выполнения ROLLUP). В зависимости от того, как сортируются значения NULL в используемой СУБД, может потребоваться добавить в решение оператор ORDER BY, чтобы получить такое же результирующее множество, как представлено выше.
DB2 и Oracle
Используйте расширение ROLLUP оператора GROUP BY и затем выражение CASE для представления данных в более удобном формате:
1 select mgr,
2 sum(case deptno when 10 then sal else 0 end) dept10,
3 sum(case deptno when 20 then sal else 0 end) dept20,
4 sum(case deptno when 30 then sal else 0 end) dept30,
5 sum(case flag when '11' then sal else null end) total
6 from (
7 select deptno,mgr,sum(sal) sal,
8 cast(grouping(deptno) as char(1))||
9 cast(grouping(mgr) as char(1)) flag
10 from emp
11 where mgr is not null
12 group by rollup(deptno,mgr)
13 ) x
14 group by mgr
|
SQL Server
Используйте расширение ROLLUP оператора GROUP BY и затем выражение CASE для представления данных в более удобном формате:
1 select mgr,
2 sum(case deptno when 10 then sal else 0 end) dept10,
3 sum(case deptno when 20 then sal else 0 end) dept20,
4 sum(case deptno when 30 then sal else 0 end) dept30,
5 sum(case flag when '11' then sal else null end) total
6 from (
7 select deptno,mgr,sum(sal) sal,
8 cast(grouping(deptno) as char(1))+
9 cast(grouping(mgr) as char(1)) flag
10 from emp
11 where mgr is not null
12 group by deptno,mgr with rollup
13 ) x
14 group by mgr
|
MySQL и PostgreSQL
Функция GROUPING не поддерживается ни одной из этих СУБД.
Обсуждение
Приведенные выше решения идентичны, за исключением строки конкатенации и описания GROUPING, поэтому промежуточные результаты обсудим на примере решения для SQL Server (все сказанное здесь будет правомочно и для DB2, и для Oracle).
Первый шаг – сформировать результирующее множество, суммируя значения SAL всех подчиненных каждого руководителя (MGR) для каждого отдела (DEPTNO). Идея в том, чтобы показать, сколько служащих подчиняется каждому руководителю в каждом отделе. Например, приведенный ниже запрос позволит сравнить заработные платы подчиненных руководителя KING из 10 отдела с заработными платами подчиненных KING из 30 отдела.
select deptno,mgr,sum(sal) sal
from emp
where mgr is not null
group by mgr,deptno
order by 1,2
DEPTNO MGR SAL
------ ---- ----
10 7782 1300
10 7839 2450
20 7566 6000
20 7788 1100
20 7839 2975
20 7902 800
30 7698 6550
30 7839 2850
|
Далее используем расширение ROLLUP оператора GROUP BY и создаем подсуммы для каждого DEPTNO и по всем служащим (которые находятся в чьем-либо подчинении):
select deptno,mgr,sum(sal) sal
from emp
where mgr is not null
group by deptno,mgr with rollup
DEPTNO MGR SAL
------ ---- ----
10 7782 1300
10 7839 2450
10 3750
20 7566 6000
20 7788 1100
20 7839 2975
20 7902 800
20 10875
30 7698 6550
30 7839 2850
30 9400
24025
|
Когда подсуммы найдены, необходим способ определить, какое из значений является подсуммой (создано ROLLUP), а какое – результатом выполнения обычного GROUP BY. С помощью функции GROUPING создайте битовые карты, помогающие отличить подсуммы от обычных агрегатов:
select deptno,mgr,sum(sal) sal,
cast(grouping(deptno) as char(1))+
cast(grouping(mgr) as char(1)) flag
from emp
where mgr is not null
group by deptno,mgr with rollup
DEPTNO MGR SAL FLAG
______ __________ __________ ____
10 7782 1300 00
10 7839 2450 00
10 3750 01
20 7566 6000 00
20 7788 1100 00
20 7839 2975 00
20 7902 800 00
20 10875 01
30 7698 6550 00
30 7839 2850 00
30 9400 01
24025 11
|
Строки со значением 00 в поле FLAG являются результатом обычной агрегации. Строки со значением 01 в поле FLAG – результаты выполнения ROLLUP, осуществляющего агрегацию SAL по DEPTNO (поскольку DEPTNO указан в списке ROLLUP первым; если изменить порядок, например «GROUP BY MGR, DEPTNO WITH ROLLUP», результаты будут совершенно иными). Строка со значением 11 в поле FLAG – результат выполнения ROLLUP, суммирующего SAL по всем строкам.
Теперь у нас есть все необходимое для создания красивого отчета с помощью простых выражений CASE. Цель – сформировать отчет, представляющий заработные платы подчиненных всех руководителей по отделам. Если в каком-то отделе данному руководителю не подчиняется ни один служащий, должен быть возвращен нуль; в противном случае требуется возвратить сумму всех заработных плат подчиненных этого руководителя в данном отделе. Кроме того, должен быть добавлен столбец TOTAL, представляющий сумму всех заработных плат. Решение, удовлетворяющее всем этим требованиям, показано ниже:
select mgr,
sum(case deptno when 10 then sal else 0 end) dept10,
sum(case deptno when 20 then sal else 0 end) dept20,
sum(case deptno when 30 then sal else 0 end) dept30,
sum(case flag when '11' then sal else null end) total
from (
select deptno,mgr,sum(sal) sal,
cast(grouping(deptno) as char(1))+
cast(grouping(mgr) as char(1)) flag
from emp
where mgr is not null
group by deptno,mgr with rollup
) x
group by mgr
order by coalesce(mgr,9999)
MGR DEPT10 DEPT20 DEPT30 TOTAL
---- ---------- ---------- ---------- ----------
7566 0 6000 0
7698 0 0 6550
7782 1300 0 0
7788 0 1100 0
7839 2450 2975 2850
7902 0 800 0
3750 10875 9400 24025
|
Любой из материалов, опубликованных на этом сервере, не может быть воспроизведен в какой бы
то ни было форме и какими бы то ни было средствами без письменного разрешения владельцев авторских
прав.
........................
"С полным содержанием данной статьи можно ознакомиться в печатной версии журнала"
Copyright © 1994-2016 ООО "К-Пресс"
Home
Поиск
Издания
Контакты
